SQL> @&1 SQL> /* @historico_proveedor.sql */ SQL> /* Este ejemplo pretende ilustrar la utilización de disparadores para auditar determinadas operaciones efectuadas en la BD. SQL> Para ello se crea un disparador que se activa con cada cambio en las tuplas de proveedor y genera una entrada en una tabla SQL> con el histórico de cambios. SQL> S.V. Univ. Zaragoza, 10 mayo 2015 SQL> */ SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SET LINESIZE 132 SQL> SET PAGESIZE 200 SQL> SQL> /* Crear la tabla para el histórico de operaciones en Proveedor SQL> */ SQL> CREATE TABLE hist_Proveedor ( 2 idOp number(5) CONSTRAINT idOp_PK PRIMARY KEY, 3 clvProv number(9) NOT NULL, 4 nombProv char(32) NOT NULL, 5 fecha date NOT NULL, 6 empleado varchar2(255) NOT NULL, 7 tpOp char(2) NOT NULL); Tabla creada. SQL> SQL> /* Crear una secuencia para generar el idOp de las operaciones SQL> */ SQL> CREATE SEQUENCE idHistOpSQ; Secuencia creada. SQL> SQL> /* Crear un disparador para generar las tuplas de las op. realizadas SQL> */ SQL> CREATE OR REPLACE TRIGGER histProveedor_TR 2 AFTER INSERT OR UPDATE OR DELETE ON Proveedor 3 FOR EACH ROW 4 DECLARE 5 idProv Proveedor.clvProv%TYPE; 6 nombre Proveedor.nombProv%TYPE; 7 Operac hist_Proveedor.tpOp%TYPE; 8 BEGIN 9 IF INSERTING THEN Operac := 'I'; idProv := :NEW.clvProv; nombre := :NEW.nombProv; 10 ELSIF DELETING THEN Operac := 'D'; idProv := :OLD.clvProv; nombre := :OLD.nombProv; 11 ELSIF UPDATING THEN Operac := 'U+'; idProv := :NEW.clvProv; nombre := :NEW.nombProv; 12 INSERT INTO hist_Proveedor VALUES (idHistOpSQ.NEXTVAL, :OLD.clvProv, :OLD.nombProv, SYSDATE, USER, 'U-'); 13 END IF; 14 15 INSERT INTO hist_Proveedor VALUES (idHistOpSQ.NEXTVAL, idProv, nombre, SYSDATE, USER, Operac); 16 END histPiezas_TR; 17 / Disparador creado. SQL> SHOW ERRORS TRIGGER histProveedor_TR; No hay errores. SQL> SQL> column empleado format A20 SQL> column nombProv format A32 SQL> column idOp format 999 SQL> SQL> DELETE FROM suministrar; 6 filas suprimidas. SQL> DELETE FROM proveedor; 3 filas suprimidas. SQL> DELETE FROM hist_Proveedor; 3 filas suprimidas. SQL> SQL> INSERT INTO Proveedor VALUES (1, 'PEREZ'); 1 fila creada. SQL> SELECT * FROM proveedor; CLVPROV NOMBPROV ---------- -------------------------------- 1 PEREZ SQL> SELECT * FROM hist_Proveedor; IDOP CLVPROV NOMBPROV FECHA EMPLEADO TP ---- ---------- -------------------------------- -------- -------------------- -- 4 1 PEREZ 12/05/15 ASIGBD I SQL> UPDATE Proveedor SET clvProv=2, nombProv='LOPEZ' WHERE clvProv=1; 1 fila actualizada. SQL> INSERT INTO Proveedor VALUES (1, 'PEREZ'); 1 fila creada. SQL> INSERT INTO Proveedor VALUES (4, 'MARIANO'); 1 fila creada. SQL> INSERT INTO Proveedor VALUES (3, 'MARTINEZ'); 1 fila creada. SQL> DELETE FROM Proveedor WHERE clvProv=4; 1 fila suprimida. SQL> SELECT * FROM proveedor; CLVPROV NOMBPROV ---------- -------------------------------- 2 LOPEZ 1 PEREZ 3 MARTINEZ SQL> SELECT * FROM hist_Proveedor; IDOP CLVPROV NOMBPROV FECHA EMPLEADO TP ---- ---------- -------------------------------- -------- -------------------- -- 4 1 PEREZ 12/05/15 ASIGBD I 5 1 PEREZ 12/05/15 ASIGBD U- 6 2 LOPEZ 12/05/15 ASIGBD U+ 7 1 PEREZ 12/05/15 ASIGBD I 8 4 MARIANO 12/05/15 ASIGBD I 9 3 MARTINEZ 12/05/15 ASIGBD I 10 4 MARIANO 12/05/15 ASIGBD D 7 filas seleccionadas. SQL> SQL> -- FINAL DEL TEST. Eliminar de la BD los elementos añadidos. SQL> SQL> DROP TRIGGER histProveedor_TR; Disparador borrado. SQL> DROP TABLE hist_Proveedor; Tabla borrada. SQL> SQL> DROP SEQUENCE idHistOpSQ; Secuencia borrada. SQL> SQL> INSERT INTO suministrar VALUES (1, 91); 1 fila creada. SQL> INSERT INTO suministrar VALUES (1, 92); 1 fila creada. SQL> INSERT INTO suministrar VALUES (2, 92); 1 fila creada. SQL> INSERT INTO suministrar VALUES (2, 93); 1 fila creada. SQL> INSERT INTO suministrar VALUES (2, 94); 1 fila creada. SQL> INSERT INTO suministrar VALUES (1, 95); 1 fila creada. SQL> set echo off fin de la ejecucion de K:\BD_2015\pruebas_ORACLE\ej_clase\historico_proveedor.sql inicio de sesión interactiva ASIGBD vicious.hendrix-oracle.cps.unizar.es fecha: 12/05/15 SQL> exit