PL/SQL. Trigger 2

1.- Ejemplo de como crear un trigger. a) Construir un disparador de base de datos que permita auditar las operaciones de inserción o borrado de datos que se realicen en la tabla emple según las siguientes especificaciones: - En primer lugar se creará desde SQL*Plus la tabla auditaremple con la columna col1 VARCHAR2(200). - Cuando se produzca cualquier manipulación se insertará una fila en dicha tabla que contendrá: - Fecha y hora - Número de empleado - Apellido - La operación de actualización INSERCIÓN o BORRADO CREATE TABLE auditaremple ( col1 VARCHAR2(200) ); CREATE OR REPLACE TRIGGER auditar_act_emp BEFORE INSERT OR DELETE ON EMPLE FOR EACH ROW BEGIN IF DELETING THEN INSERT INTO AUDITAREMPLE VALUES(TO_CHAR(sysdate,'DD/MM/YY*HH24:MI*') || :OLD.EMP_NO|| '*' || :OLD.APELLIDO || '* BORRADO '); ELSIF INSERTING THEN INSERT INTO AUDITAREMPLE VALUES(TO_CHAR(sysdate,'DD/MM/YY*HH24:MI*') || :NEW.EMP_NO || '*' || :NEW.APELLIDO||'* INSERCION '); END IF; END;

b)Ejemplo de como crear un trigger cuando actualizamos en la tabla datos. 2.- Escribir un trigger de base de datos un que permita auditar las modificaciones en la tabla empleados insertado en la tabla auditaremple los siguientes datos: - Fecha y hora - Número de empleado - Apellido - La operación de actualización: MODIFICACIÓN. - El valor anterior y el valor nuevo de cada columna modificada. (solo las columnas modificadas) CREATE OR REPLACE TRIGGER audit_modif BEFORE UPDATE ON EMPLE FOR EACH ROW DECLARE v_cad_inser auditaremple.col1%TYPE; BEGIN v_cad_inser := TO_CHAR(sysdate,'DD/MM/YY*HH24:MI*') ||:OLD.EMP_NO ||'* MODIFICACION *'; IF UPDATING ('EMP_NO') THEN v_cad_inser := v_cad_inser ||:OLD.EMP_NO|| '*'|| :NEW.EMP_NO; END IF; IF UPDATING ('APELLIDO') THEN v_cad_inser := v_cad_inser ||:OLD.APELLIDO|| '*'||:NEW.APELLIDO; END IF; IF UPDATING ('OFICIO') THEN v_cad_inser := v_cad_inser ||:OLD.OFICIO|| '*'||:NEW.OFICIO; END IF; IF UPDATING ('DIR') THEN v_cad_inser := v_cad_inser ||:OLD.DIR|| '*'||:NEW.DIR; END IF; IF UPDATING ('FECHA_ALT') THEN v_cad_inser := v_cad_inser ||:OLD.FECHA_ALT||:NEW.FECHA_ALT; END IF; IF UPDATING ('SALARIO') THEN v_cad_inser := v_cad_inser ||:OLD.SALARIO|| '*'||:NEW.SALARIO; END IF; IF UPDATING ('COMISION') THEN v_cad_inser := v_cad_inser ||:OLD.COMISION|| '*'||:NEW.COMISION; END IF; IF UPDATING ('DEPT_NO') THEN v_cad_inser := v_cad_inser ||:OLD.DEPT_NO|| '*'||:NEW.DEPT_NO; END IF; INSERT INTO AUDITAREMPLE VALUES(v_cad_inser); END; 3.- Escribir un disparador de base de datos que haga fallar cualquier operación de modificación del apellido o del número de un empleado, o que suponga una subida de sueldo superior al 10%. CREATE OR REPLACE TRIGGER fallo_modif BEFORE UPDATE OF apellido, emp_no, salario ON emple FOR EACH ROW BEGIN IF UPDATING('emp_no') OR UPDATING('apellido') OR (UPDATING ('salario') AND :new.salario>:old.salario*1.1) THEN RAISE_APPLICATION_ERROR (-20001,'Err. Modificacion no permitida'); END IF; END;

c) Ejemplos como crear un trigger a partir de una vista. 4.- Suponiendo que disponemos de la vista 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; Construir un disparador que permita realizar operaciones de actualización en la tabla depart a partir de la vista dptos, de forma similar al ejemplo del trigger t_ges_emplead. Se contemplarán las siguientes operaciones: - Insertar departamento. - Borrar departamento. - Modificar la localidad de un departamento. CREATE OR REPLACE TRIGGER ges_depart INSTEAD OF DELETE OR INSERT OR UPDATE ON DEPARTAM FOR EACH ROW BEGIN IF DELETING THEN DELETE FROM depart WHERE dept_no = :old.dept_no; ELSIF INSERTING THEN INSERT INTO depart VALUES(:new.dept_no, :new.dnombre, :new.loc); ELSIF UPDATING('loc') THEN UPDATE depart SET loc = :new.loc WHERE dept_no = :old.dept_no; ELSE RAISE_APPLICATION_ERROR (-20001,'Error en la actualización'); END IF; END;