insert into emple (emp_no, apellido, dept_no) values(1000, 'ruiz', 10); create or replace trigger t1 before insert on emple begin dbms_output.put_line('Se va a modificar la tabla'); end; / -----------------------------------------haz un disparador donde al insertar un nuevo amepleado en la tabla emple se guar de el usuario y la fecha en la tabla auditar create table auditar( usuario varchar2(15), fecha date ); create or replace trigger t2 before insert on emple begin end; /
insert into auditar values(user, sysdate);
----------------------------------------------------------guarda los datos de una tabla que despues borres en otra tabla diferente -- para crear una copia vacia de una tabla create table emple2 as select * from emple where emp_no=0; create or replace trigger t3 before delete on emple for each row begin insert into emple2 values( :old.emp_no, :old.apellido, :old.oficio, :old.dir, :old.fecha_alt, :old. salario, :old.comision, :old.dept_no ); dbms_output.put_line(:old.emp_no||' '||:old.dept_no); end; / ---ejer 3 ----haz un trigger de tal forma que al borrar un departamento traslade "antes" a tod os sus empleados al departamento 40.
create or replace trigger t4 before delete on depart for each row begin update emple set dept_no=40 where dept_no=:old.dept_no; end; / -------------------------------1. Al borrar, editar o insertar un producto en la tabla PRODUCTOS guardará todos s us datos en la tabla AUDITAR_PRODUCTOS. Guardará también el nombre del usuario que realizó la acción y la fecha. Esta tabla tiene los mismos campos que PRODUCTOS y además USUARIO y FECHA. DROP TABLE AUDITAR_PRODUCTOS; create table auditar_productos as select * from productos where cod_producto=0; alter table auditar_productos add(usuario varchar(15) DEFAULT USER, fecha date D EFAULT SYSDATE); ALTER TABLE VENTAPRO DISABLE CONSTRAINT FK_COD_PRODUCTO create or replace trigger t6 before delete OR INSERT OR UPDATE on productos for each row begin IF INSERTING THEN INSERT INTO AUDITAR_PRODUCTOS(COD_PRODUCTO, DESCRIPCION, LINEA_P RODUCTO, PRECIO) VALUES(:NEW.COD_PRODUCTO, :NEW.DESCRIPCION, :NEW.LINEA_PRODUCTO, :NEW.PRECIO); ELSIF DELETING THEN INSERT INTO AUDITAR_PRODUCTOS(COD_PRODUCTO, DESCRIPCION, LINEA_P RODUCTO, PRECIO) VALUES(:OLD.COD_PRODUCTO, :OLD.DESCRIPCION, :OLD.LINEA_PRODUCTO, :OLD.PRECIO); ELSIF UPDATING THEN INSERT INTO AUDITAR_PRODUCTOS(COD_PRODUCTO, DESCRIPCION, LINEA_P RODUCTO, PRECIO) VALUES(:OLD.COD_PRODUCTO, :OLD.DESCRIPCION, :OLD.LINEA_PRODUCTO, :OLD.PRECIO); INSERT INTO AUDITAR_PRODUCTOS(COD_PRODUCTO, DESCRIPCION, LINEA_P RODUCTO, PRECIO) VALUES(:NEW.COD_PRODUCTO, :NEW.DESCRIPCION, :NEW.LINEA_PRODUCTO, :NEW.PRECIO); END IF; end; / ALTER TRIGGER T1 DISABLE; ENABLE; 3. Haz un trigger que al borrar, editar o insertar un alumno de la tabla ALUMNOS guarde su DNI, nombre completo, el usuario que lo borró, la fecha y el tipo de sentencia lleva a cabo (INSERT, UP DATE, DELETE).
Se guardará en la tabla AUDITAR_ALUMNOS que contará con los siguientes campos: DNI, APENOM, USUARIO, FECHA, SENTENCIA. (Determina el tipo de dato correcto para cada columna). CREATE TABLE AUDITAR_ALUMNOS( DNI vARCHAR2(10), APENOM VARCHAR2(30), USUARIO VARCHAR(20), FECHA DATE, SENTENCIA VARCHAR(10) ); CREATE OR REPLACE TRIGGER T8 BEFORE DELETE OR INSERT OR UPDATE ON ALUMNOS FOR EACH ROW BEGIN IF INSERTING THEN INSERT INTO AUDITAR_ALUMNOS VALUES(:NEW.DNI, SYSDATE,'INSERT'); ELSIF DELETING THEN INSERT INTO AUDITAR_ALUMNOS VALUES(:OLD.DNI, SYSDATE,'DELETE'); ELSIF UPDATING THEN INSERT INTO AUDITAR_ALUMNOS VALUES(:OLD.DNI, SYSDATE,'UPDATE_OLD'); INSERT INTO AUDITAR_ALUMNOS VALUES(:NEW.DNI, SYSDATE,'UPDATE_NEW'); END IF; END; /
:NEW.APENOM, USER, :OLD.APENOM, USER, :OLD.APENOM, USER, :NEW.APENOM, USER,
4 Al borrar, editar o9 insertar un profesor en la tabla PROFESORES guardará todos sus datos en la tabla AUDITAR_profes. Guardará tambien el nombre del usuario que realizo la accion y la fecha. esta tabla tiene los mismos campos que PROFESORES y ademas USUARIO y FECHA. drop TABLE auditar_profes; create table auditar_profes ( cod_centro number(4), dni number(10), apellidos varchar2(30), especialidad varchar2(16), usuario varchar2(16), fecha date, accion varchar2(15) ); create or replace trigger t10 after delete or insert or update on profesores for each row begin if deleting then insert into auditar_profes values(:old.cod_centro,:old.d ni,:old.apellidos,:old.especialidad,user,sysdate,'DELETE'); elsif inserting then insert into auditar_profes values(:new.cod_centro,:new.d ni,:new.apellidos,:new.especialidad,user,sysdate,'INSERT'); elsif updating then insert into auditar_profes values(:old.cod_centro,:old.d ni,:old.apellidos,:old.especialidad,user,sysdate,'UPDATE_OLD');
insert into auditar_profes values(:new.cod_centro,:new.d ni,:new.apellidos,:new.especialidad,user,sysdate,'UPDATE_NEW'); end if; end; / triggers de sustitucioooooooooon 7 crea la vista vistadep(emp_no, apellido, dept_no)y crea el trigger de sustituc ion necesario para insertar los datos en la tabla create or replace view vistadep as select emp_no, apellido, dnombre from emple e, depart d where e.dept_no=d.dept_no; create or replace trigger t_vistadep instead of insert on vistadep declare v_dept_no emple.dept_no%type; begin select dept_no into v_dept_no from depart where dnombre=:new.dnombre;
end; /
insert into emple(emp_no, apellido,dept_no) values(:new.emp_no, :new.apellido, v_dept_no);
insert into vistadep values(9000,'gili','VENTAS'); 12 haz una vista donde veamos el numero del departamento, su nombre y el numero total de empleados que trabajan en el. haz el trigger necesario para ingresar datos en esta vista (excepto el calculado ). create or replace view numero as select d.dept_no, dnombre, count(emp_no) as numero_total from emple e, depart d where d.dept_no=e.dept_no (+) group by d.dept_no, dnombre; create or replace trigger t_numero instead of insert on numero declare begin insert into depart(dept_no, dnombre) values(:new.dept_no, :new.dnombre); end; / insert into numero (dept_no, dnombre) values(50,club'); 13. Haz una vista donde visualizaremos el articulo, cod_fabricante, peso, categoria y beneficio(precio_venta - Precio_costo.) haz el trigger necesario que permite insertar datos en la vista. create or replace view acpcb as select articulo, cod_fabricante, peso, categoria, (precio_venta - precio_costo) as beneficio from articulos;
create or replace trigger t_acpcb instead of insert on acpcb begin insert into articulos values(:new.articulo, :new.cod_fabricante, :new.pe so, :new.categoria,null,null,null); end; / insert into acpcb values('perro',999,34,'PRIMERA',null); 13.b actializar el trigger anterior para poder borrar tambien filas en la tabla artic ulos. create or replace trigger t_acpcb instead of insert or update on acpcb begin if inserting then insert into articulos values(:new.articulo, :new.cod_fabricante, :new.pe so, :new.categoria,null,null,null); elsif deleting then delete articulos where :old.articulo=articulo and :old.cod_fabricante=co d_fabricante and :old.peso=peso and :old.categoria=categoria; end if; end; / 2. Haz la vista EMP_DEP con los campos EMP_NO, APELLIDO, SALARIO, OFICIO, DNOMBR E con los datos de las tablas EMPLE y DEPART. Haz un trigger de sustitución que nos permita borrar, insertar y editar cualquier campo. CREATE OR REPLACE VIEW EMP_DEP AS SELECT EMP_NO, APELLIDO, SALARIO, OFICIO, DNOMBRE FROM EMPLE E, DEPART D WHERE E.DEPT_NO=D.DEPT_NO; CREATE OR REPLACE TRIGGER T_EMP_DEP INSTEAD OF DELETE OR INSERT OR UPDATE ON EMP_DEP DECLARE V_DEPT_NO DEPART.DEPT_NO%TYPE; BEGIN IF DELETING THEN DELETE EMPLE WHERE EMP_NO=:OLD.EMP_NO; ELSIF INSERTING THEN ----SELECT DEPT_NO INTO V_DEPT_NO FROM DEPART WHERE DNOMBRE=:NEW.DNOMBRE; ----INSERT INTO EMPLE(EMP_NO, APELLIDO, SALARIO, OFICIO, DEPT_NO) VALUES(:NEW.EMP_NO, :NEW.APELLIDO, :NEW.SALARIO, :NEW.OFICIO, V_ DEPT_NO); ELSIF UPDATING THEN ---SELECT DEPT_NO INTO V_DEPT_NO FROM DEPART WHERE DNOMBRE=:NEW.DNOMBRE; -----
UPDATE EMPLE SET EMP_NO=:NEW.EMP_NO, APELLIDO=:NEW.APELLIDO, SAL ARIO=:NEW.SALARIO, OFICIO=:NEW.OFICIO, dept_no=v_dept_no WHERE EMP_NO=:OLD.EMP_NO; END IF; END; / insert into emp_deP values(1, 'PEPE', 1000, 'VENDEDOR', 'PRUEBA'); delete emp_dep where salario<2000; update emp_dep set dnombre='NADA' where emp_no = 7934;
3.- del libro pag 354; CREATE VIEW DEPARTAM AS SELECT DEPART.DEPT_NO, DNOMBRE, LOC, COUNT(EMP_NO) TOT_EMPLE FROM EMPLE, DEPART WHERE EMPLE.DEPT_NO (+) = DEPART.DEPT_NO GROUP BY DEPART.DEPT_NO, DNOMBRE, LOC; Construye un disparador que permita realizar actualizaciones en la tabla departa partir de la vista departam, de forma similar al ejemplo del trigger t_ges_emplead. Se contemplarán las siguientes operaciones: ± ±
Insertar y borrar departamento. Modificar la localidad de un departamento
create or replace trigger t_departam instead of insert or delete or update on departam begin if inserting then insert into depart(dept_no, dnombre, loc) values(:new.dept_no, :new.dnombre, :new.loc); elsif deleting then delete depart where dept_no=:old.dept_no; elsif updating ('LOC') then update depart set loc=:new.loc where dept_no=:old.dept_no; end if; end; / 7. Usa las tablas de ALUMNOS, NOTAS y ASIGNATURAS. Haz una vista de las tres tablas donde veamos DNI, APENOM, NOMBRE (de la asi gnatura) y NOTA. Haz un trigger que nos permita realizar cualquier operación sobre la vista: matricular alumnos existentes, actualizar una nota o borrar la matricula de un alumno existente. create or replace view v_20 as select al.dni, apenom, nombre, nota from alumnos al, notas n, asignaturas ag where al.dni=n.dni and n.cod=ag.cod
order by dni ; create or replace trigger t_20 instead of insert or delete or update on V_20 declare v_cod asignaturas.cod%type; begin if inserting then select cod into v_cod from asignaturas where nombre=:new.nombre; insert into notas values(:new.dni,v_cod, null); elsif deleting then select cod into v_cod from asignaturas where nombre=:old.nombre; delete notas where dni=:old.dni and cod=v_cod; elsif updating ('NOTA')then update notas set nota=:new.nota where dni=:old.dni and cod=v_cod; end if; end; / 5. Modifica el trigger del ejercicio 2 de tal forma que si el departamento no e xiste, lo da de alta en la tabla DEPART asociándole como DEPT_NO la siguiente decena dis ponible. Posteriormente dará de alta al empleado en dicho departamento. CREATE OR REPLACE VIEW EMP_DEP AS SELECT EMP_NO, APELLIDO, SALARIO, OFICIO, DNOMBRE FROM EMPLE E, DEPART D WHERE E.DEPT_NO=D.DEPT_NO; CREATE OR REPLACE TRIGGER T_EMP_DEP INSTEAD OF DELETE OR INSERT OR UPDATE ON EMP_DEP DECLARE V_DEPT_NO DEPART.DEPT_NO%TYPE; BEGIN IF DELETING THEN DELETE EMPLE WHERE EMP_NO=:OLD.EMP_NO; ELSIF INSERTING THEN ----SELECT DEPT_NO INTO V_DEPT_NO FROM DEPART WHERE DNOMBRE=:NEW.DNOMBRE; ----INSERT INTO EMPLE(EMP_NO, APELLIDO, SALARIO, OFICIO, DEPT_NO) VALUES(:NEW.EMP_NO, :NEW.APELLIDO, :NEW.SALARIO, :NEW.OFICIO, V_ DEPT_NO); ELSIF UPDATING THEN ---SELECT DEPT_NO INTO V_DEPT_NO FROM DEPART WHERE DNOMBRE=:NEW.DNOMBRE; ----UPDATE EMPLE SET EMP_NO=:NEW.EMP_NO, APELLIDO=:NEW.APELLIDO, SAL ARIO=:NEW.SALARIO, OFICIO=:NEW.OFICIO, dept_no=v_dept_no WHERE EMP_NO=:OLD.EMP_NO; END IF;
END; /
6. Crea la tabla de contabilidad de los departamentos CONT_DEP con el número, el nombre del departamento y el sueldo medio (salario + comisión) de cad a departamento. Haz un trigger tal que cuando insertemos, borremos o modifiquemos el DEPT_NO o el SALARIO o la COMISIÓN de la tabla EMPLE pase los nuevos datos calculados a la tabla CONT_DEP. create table CONT_DEP as select d.dept_no, dnombre, round(avg(salario + nvl(comision,0)),1) as sueldo_me dio from emple e, depart d where d.dept_no=e.dept_no group by d.dept_no, dnombre;
