drop table libros;create table libros( codigo number(6), titulo varchar2(40), autor varchar2(30), precio number(6,2) );drop table control;create table control( usuario varchar2(30), fecha date );select * from libros;create or replace trigger tr_ingresar_libros before insert on libros begin insert into Control values(user,sysdate); end tr_ingresar_libros;select * from libros;alter session set NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI';select *from user_triggers where trigger_name ='TR_INGRESAR_LIBROS';insert into libros values(100,'Uno','Richard Bach',25);select *from control;insert into libros values(150,'Matematica estas ahi','Paenza',12);insert into libros values(185,'El aleph','Borges',42);select *from control;

/*PROBLEMA 2*/

drop table lib; drop table cont;create table lib(codigo number(6), titulo varchar2(40), autor varchar2(30), editorial varchar2(20), precio number(6,2) );create table cont( usuario varchar2(30), fecha date );insert into lib values(97,'Uno','Richard Bach','Planeta',25);insert into lib values(98,'El aleph','Borges','Emece',28);insert into lib values(99,'Matematica estas ahi','Paenza','Nuevo siglo',12);insert into lib values(100,'Aprenda PHP','Molina Mario','Nuevo siglo',55);insert into lib values(101,'Alicia en el pais de las maravillas','Carroll','Planeta',35);insert into lib values(102,'El experto en laberintos','Gaskin','Planeta',22);alter session set NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI';select *from lib;create or replace trigger tr_borrar_lib before delete on lib for each row begin insert into control values(user,sysdate); end tr_borrar_lib;select *from lib;select *from user_triggers where trigger_name ='TR_BORRAR_LIBROS';delete from lib where codigo<100;select *from cont;create or replace trigger tr_borrar_libros before delete on libros begin insert into control values(user,sysdate); end tr_borrar_libros;select *from cont;select *from user_triggers where trigger_name ='TR_BORRAR_LIBROS';delete from lib where editorial='Planeta';select *from cont;

/*problema 3*/

drop table libros5; drop table control5;create table libros5( codigo number(6), titulo varchar2(40), autor varchar2(30), editorial varchar2(20), precio number(6,2) );create table control5( usuario varchar2(30), fecha date );insert into libros5 values(100,'Uno','Richard Bach','Planeta',25); insert into libros5 values(103,'El aleph','Borges','Emece',28);insert into libros5 values(105,'Matematica estas ahi','Paenza','Nuevo siglo',12); insert into libros5 values(120,'Aprenda PHP','Molina Mario','Nuevo siglo',55);insert into libros5 values(145,'Alicia en el pais de las maravillas','Car');

alter session set NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI';select * from libros5;create or replace trigger tr_actualizar_libros5 before update on libros5 begin insert into control5 values(user,sysdate); end tr_actualizar_libros5;select * from libros5;select *from user_triggers where trigger_name ='TR_ACTUALIZAR_LIBROS';select * from libros5;update libros5 set codigo=99 where codigo=100;select *from control5;update libros5 set precio=precio+precio*0.1 where editorial='Nuevo siglo';select *from control5; /*problema 4*/drop table libr; drop table control;create table libr( codigo number(6), titulo varchar2(40), autor varchar2(30), editorial varchar2(20), precio number(6,2) );create table contr( usuario varchar2(30), fecha date, operacion varchar2(20) );insert into libr values(100,'Uno','Richard Bach','Planeta',25); insert into libr values(103,'El aleph','Borges','Emece',28);insert into libr values(105,'Matematica estas ahi','Paenza','Nuevo siglo',12);insert into libr values(120,'Aprenda PHP','Molina Mario','Nuevo siglo',55);insert into libr values(145,'Alicia en el pais de las maravillas','Carroll','Planeta',35);alter session set NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI';select * from libr;create or replace trigger tr_cambios_libr before insert or update or delete on libr for each row begin if inserting then insert into contr values (user, sysdate,'inserción'); end if; if updating then insert into contr values (user, sysdate,'actualización'); end if; if deleting then insert into contr values (user, sysdate,'borrado'); end if; end tr_cambios_libr;select * from libr;select *from user_triggers where trigger_name ='TR_CAMBIOS_LIBROS';insert into libr values(150,'El experto en laberintos','Gaskin','Planeta',23);select *from contr;update libr set precio=precio+precio*0.1 where editorial='Planeta';select *from contr;delete from libr where codigo=145;select *from contr;

/*problema 5*/drop table libros6;create table libros6( codigo number(6), titulo varchar2(40), autor varchar2(30), editorial varchar2(20), precio number(6,2) );insert into libros6 values(100,'Uno','Richard Bach','Planeta',25); insert into libros6 values(103,'El aleph','Borges','Planeta',40); insert into libros6 values(105,'Matematica estas ahi','Paenza','Nuevo siglo',12); insert into libros6 values(120,'Aprenda PHP','Molina Mario','Nuevo siglo',55);select * from libros6;create or replace trigger tr_precio_libros6 before insert or update of precio on libros6 for each row when(new.precio>50) begin :new.precio := round(:new.precio); end tr_precio_libros6;select * from libros6;insert into libros6 values(250,'El experto en laberintos','Gaskin','Emece',30.80);select *from libros6 where titulo like '%experto%';insert into libros6 values(300,'Alicia en el pais de las maravillas','Carroll','

Emece',55.6);select *from libros6 where titulo like '%maravillas%';update libros6 set precio=40.30 where codigo=105;select *from libros6 where codigo =105;update libros6 set precio=50.30 where codigo=105;select *from libros6 where codigo=105;update libros6 set precio=50.30 where editorial='Nuevo siglo';select *from libros6 where editorial='Nuevo siglo';update libros6 set precio=precio+15.8 where editorial='Planeta';select *from libros6 where editorial='Planeta';set serveroutput on; execute dbms_output.enable(20000);create or replace trigger tr_precio_libros6 before insert or update of precio on libros6 for each row begin dbms_output.put_line('Trigger disparado'); if :new.precio>50 then dbms_output.put_line('Precio redondeado'); :new.precio:= round(:new.precio); end if; end tr_precio_libros6;insert into libros6 values(350,'Ilusiones','Bach','Planeta',20.35);insert into libros6 values(380,'El anillo del hechicero','Gaskin','Planeta',60.35);select *from user_triggers where trigger_name ='TR_LIBROS6';

/*problema 6*/drop table controlprecios; drop table libros7; drop table control7;create table libros7( codigo number(6), titulo varchar2(40), autor varchar2(30), editorial varchar2(20), precio number(6,2), stock number(4) );create table control7( usuario varchar2(30), fecha date, codigo number(6) );create table controlprecios( fecha date, codigo number(6), precioanterior number(6,2), precionuevo number(6,2) );insert into libros7 values(100,'Uno','Richard Bach','Planeta',25,100); insert into libros7 values(103,'El aleph','Borges','Emece',28,0);insert into libros7 values(105,'Matematica estas ahi','Paenza','Nuevo siglo',12,50);insert into libros7 values(120,'Aprenda PHP','Molina Mario','Nuevo siglo',55,200);insert into libros7 values(145,'Alicia en el pais de las maravillas','Carroll','Planeta',35,10);select * from libros7;create or replace trigger tr_actualizar_libros before update on libros7 for each row begin if updating ('precio') then insert into controlprecios values(sysdate,:old.codigo,:old.precio,:new.precio); else insert into control7 values(user,sysdate,:old.codigo); end if; end tr_actualizar_libros;select * from libros7;update libros7 set precio=35 where codigo=100;select *from controlprecios;select *from control7;update libros7 set stock=0 where codigo=145;select *from control7;select *from controlprecios;

/*problema 7*/

drop table empleados; drop table controlCambios;create table empleados( documento char(8) not null, nombre varchar2(30) not null, domicilio varchar2(30), seccion varchar2(20) );create table controlCambios( usuario varchar2(30), fecha date, datoanterior varchar2(30), datonuevo varchar2(30) );insert into empleados values('22222222','Ana Acosta','Bulnes 56','Secretaria');

insert into empleados values('23333333','Bernardo Bustos','Bulnes 188','Contaduria');insert into empleados values('24444444','Carlos Caseres','Caseros 364','Sistemas'); insert into empleados values('25555555','Diana Duarte','Colon 1234','Sistemas'); insert into empleados values('26666666','Diana Duarte','Colon 897','Sistemas'); insert into empleados values('27777777','Matilda Morales','Colon 542','Gerencia');select * from empleados;create or replace trigger tr_actualizar_empleados before update on empleados for each row begin if updating('documento') then insert into controlCambios values(user,sysdate, :old.documento, :new.documento); end if; if updating('nombre') then insert into controlCambios values(user,sysdate, :old.nombre, :new.nombre); end if; if updating('domicilio') then insert into controlCambios values(user,sysdate, :old.domicilio, :new.domicilio); end if; if updating('seccion') then insert into controlCambios values(user,sysdate, :old.seccion, :new.seccion); end if; end tr_actualizar_empleados;select * from empleados;create or replace trigger tr_ingresar_empleados before insert on empleados for each row begin insert into controlCambios values(user,sysdate, null, :new.documento); end tr_ingresar_empleados;create or replace trigger tr_eliminar_empleados before delete on empleados for each row begin insert into controlCambios values(user,sysdate, :old.documento, null); end tr_eliminar_empleados;select * from empleados;select trigger_name, triggering_event, status from user_triggers where trigger_name like 'TR%EMPLEADOS';insert into empleados values('28888888','Pedro Perez','Peru 374','Secretaria'); select *from controlCambios;alter trigger tr_ingresar_empleados disable;select trigger_name, status from user_triggers where trigger_name like 'TR%EMPLEADOS';insert into empleados values('29999999','Rosa Rodriguez','Rivadavia 627','Secretaria'); select *from controlCambios;update empleados set domicilio='Bulnes 567' where documento='22222222'; select *from controlCambios;alter trigger tr_actualizar_empleados disable;select trigger_name, status from user_triggers where trigger_name like 'TR%EMPLEADOS';delete from empleados where documento= '29999999'; select *from controlCambios;alter trigger tr_eliminar_empleados disable;select trigger_name, status from user_triggers where table_name = 'EMPLEADOS';delete from empleados where documento= '28888888'; select *from controlCambios;alter trigger tr_actualizar_empleados enable;update empleados set seccion='Sistemas' where documento='23333333'; select *from controlCambios;alter table empleados enable all triggers;select trigger_name, triggering_event, status from user_triggers where table_name = 'EMPLEADOS';.

/*problema 7*/drop table libros8; drop table control8;create table libros8( codigo number(6), titulo varchar2(40), autor varchar2(30), editorial varchar2(20), precio number(6,2) );create table control8( usuario varchar2(30), fecha date, operacion varchar2(20) );select * from libros8;create or replace trigger tr_ingresar_libros before insert on libros for each

row begin insert into control values(user,sysdate,'insercion'); end tr_ingresar_libros;create or replace trigger tr_actualizar_libros before update on libros for each row begin if updating('codigo') then insert into control values(user,sysdate,'codigo'); end if; if updating('titulo') then insert into control values(user,sysdate,'titulo'); end if; if updating('autor') then insert into control values(user,sysdate,'autor'); end if; if updating('editorial') then insert into control values(user,sysdate,'editorial'); end if; if updating('precio') then insert into control values(user,sysdate,'precio'); end if; end tr_actualizar_libros;create or replace trigger tr_eliminar_libros before delete on libros for each row begin insert into control values(user,sysdate,'borrado'); end tr_eliminar_libros;select * from libros8;select trigger_name, triggering_event, status from user_triggers where table_name = 'LIBROS';insert into libros8 values(100,'Uno','Richard Bach','Planeta',25); insert into libros8 values(101,'El aleph','Borges','Emece',28); insert into libros8 values(102,'Matematica estas ahi','Paenza','Nuevo siglo',12);insert into libros8 values(103,'Aprenda PHP','Molina Mario','Nuevo siglo',55);insert into libros8 values(144,'Alicia en el pais de las maravillas','Carroll','Planeta',35);select *from control8;update libros8 set editorial='Sudamericana' where editorial='Planeta'; select *from control8;delete from libros8 where codigo=101; select *from control8;update libros8 set autor='Adrian Paenza' where autor='Paenza'; select *from control8;drop table libros8;select trigger_name, triggering_event, status from user_triggers where table_name = 'LIBROS';