SQL> @&1 SQL> /* @ej1b_trigger.sql */ SQL> /* Este ejemplo pretende ilustrar las diferencias entre los diferentes modos de especificar SQL> los eventos en disparadores (a nivel de sentencia o a nivel de tupla y BEFORE o AFTER) y SQL> mostrar el orden en que se ejecutan. Para ello se ha implementado un disparador de cada SQL> tipo que muestran un mensaje cuando se ejecuta una sentencia de eliminar un suministro y SQL> opcionalmente se genera una excepción (por defecto sólo se ha activado en el disparador SQL> de después de ejecutar la sentencia). Pretende ilustrar algunos modos de gestionar las SQL> excepciones. SQL> S.V. Univ. Zaragoza, 10 mayo 2015 SQL> */ SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SET linesize 255 SQL> SET pagesize 100 SQL> SQL> -- modificar el ancho para columnas de tipo LONG (p.e. el cuerpo de un trigger) SQL> SET LONG 800 SQL> SQL> -- mostrar las tuplas de suministrar SQL> SELECT * FROM Suministrar; CLVPROV CLVPIEZA ---------- ---------- 1 91 1 92 2 92 2 93 2 94 1 95 2 95 7 filas seleccionadas. SQL> SQL> /* Crear un disparador para ilustrar BEFORE a nivel de sentencia SQL> */ SQL> CREATE OR REPLACE TRIGGER tr_BefDel_Sum 2 BEFORE DELETE ON Suministrar 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE('accion BEFORE DELETE en tabla Suministrar'); 5 -- raise_application_error( -20501, 'error antes de iniciar el borrado en Suministrar'); 6 END tr_BefDel_Sum; 7 / Disparador creado. SQL> SHOW ERRORS TRIGGER tr_BefDel_Sum No hay errores. SQL> SQL> /* Crear un disparador para ilustrar BEFORE a nivel de tupla SQL> */ SQL> CREATE OR REPLACE TRIGGER tr_BefDelRow_Sum 2 BEFORE DELETE ON Suministrar 3 FOR EACH ROW 4 BEGIN 5 DBMS_OUTPUT.PUT_LINE('accion BEFORE DELETE el suministro (' || :OLD.clvProv || ', ' || :OLD.clvPieza || ')'); 6 -- raise_application_error( -20502, 'error antes de borrar tupla en Suministrar'); 7 END tr_BefDelRow_Sum; 8 / Disparador creado. SQL> SHOW ERRORS TRIGGER tr_BefDelRow_Sum No hay errores. SQL> SQL> /* Crear un disparador para ilustrar AFTER a nivel de tupla SQL> */ SQL> CREATE OR REPLACE TRIGGER tr_AftDelRow_Sum 2 AFTER DELETE ON Suministrar 3 FOR EACH ROW 4 BEGIN 5 DBMS_OUTPUT.PUT_LINE('accion AFTER DELETE el suministro (' || :OLD.clvProv || ', ' || :OLD.clvPieza || ')'); 6 -- raise_application_error( -20503, 'error despues de borrar tupla en Suministrar'); 7 END tr_AftDelRow_Sum; 8 / Disparador creado. SQL> SHOW ERRORS TRIGGER tr_AftDelRow_Sum No hay errores. SQL> SQL> /* Crear un disparador para ilustrar AFTER a nivel de sentencia SQL> */ SQL> CREATE OR REPLACE TRIGGER tr_AftDel_Sum 2 AFTER DELETE ON Suministrar 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE('accion AFTER DELETE en tabla Suministrar'); 5 raise_application_error( -20504, 'error despues de borrar en Suministrar'); 6 END tr_AftDel_Sum; 7 / Disparador creado. SQL> SHOW ERRORS TRIGGER tr_AftDel_Sum No hay errores. SQL> SQL> column TRIGGER_NAME heading "nombre" format a20; SQL> column TRIGGER_TYPE heading "tipo" format a20; SQL> column TRIGGERING_EVENT heading "evento" format a60; SQL> column TRIGGER_BODY heading "accion" format a115; SQL> SQL> /* mostrar todos los disparadores del usuario */ SQL> -- select * from USER_TRIGGERS; SQL> SELECT TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT FROM USER_TRIGGERS; nombre tipo evento -------------------- -------------------- ------------------------------------------------------------ ACCESOSERVICIO AFTER EACH ROW INSERT GANARETAPA AFTER EACH ROW INSERT OR UPDATE OR DELETE NUEVATARJETA BEFORE EACH ROW INSERT TR_AFTDELROW_SUM AFTER EACH ROW DELETE TR_AFTDEL_SUM AFTER STATEMENT DELETE TR_BEFDELROW_SUM BEFORE EACH ROW DELETE TR_BEFDEL_SUM BEFORE STATEMENT DELETE UPDATE_MATRDXT AFTER STATEMENT INSERT OR UPDATE OR DELETE 8 filas seleccionadas. SQL> SQL> column OBJECT_NAME heading "nombre" format a18; SQL> column DATA_OBJECT_ID heading "id" format a2; SQL> column SUBOBJECT_NAME heading "subobjeto" format a9; SQL> SQL> /* mostrar todos los disparadores del usuario */ SQL> SELECT * FROM user_objects WHERE object_type='TRIGGER'; nombre subobjeto OBJECT_ID id OBJECT_TYPE CREATED LAST_DDL TIMESTAMP STATUS T G S ------------------ --------- ---------- ---------- ------------------ -------- -------- ------------------- ------- - - - ACCESOSERVICIO 2325415 TRIGGER 15/07/14 15/07/14 2014-07-15:19:00:59 VALID N N N GANARETAPA 2307442 TRIGGER 23/05/14 23/05/14 2014-05-23:12:23:16 VALID N N N NUEVATARJETA 2325414 TRIGGER 15/07/14 15/07/14 2014-07-15:19:00:59 VALID N N N TR_AFTDELROW_SUM 2376938 TRIGGER 11/05/15 11/05/15 2015-05-11:22:06:48 VALID N N N TR_AFTDEL_SUM 2376939 TRIGGER 11/05/15 11/05/15 2015-05-11:22:06:48 VALID N N N TR_BEFDELROW_SUM 2376937 TRIGGER 11/05/15 11/05/15 2015-05-11:22:06:48 VALID N N N TR_BEFDEL_SUM 2376936 TRIGGER 11/05/15 11/05/15 2015-05-11:22:06:47 VALID N N N UPDATE_MATRDXT 2311698 TRIGGER 03/06/14 03/06/14 2014-06-03:02:55:24 VALID N N N 8 filas seleccionadas. SQL> SQL> /* mostrar todos los disparadores sobre la tabla Suministrar */ SQL> SELECT TRIGGER_NAME, TABLE_OWNER FROM USER_TRIGGERS WHERE TABLE_NAME = 'SUMINISTRAR'; nombre TABLE_OWNER -------------------- ------------------------------ TR_AFTDELROW_SUM ASIGBD TR_AFTDEL_SUM ASIGBD TR_BEFDELROW_SUM ASIGBD TR_BEFDEL_SUM ASIGBD SQL> SQL> /* mostrar el cuerpo de todos los disparadores sobre la tabla Suministrar */ SQL> SELECT TRIGGER_NAME, TRIGGER_BODY FROM USER_TRIGGERS WHERE TABLE_NAME = 'SUMINISTRAR'; nombre accion -------------------- ------------------------------------------------------------------------------------------------------------------- TR_AFTDELROW_SUM BEGIN DBMS_OUTPUT.PUT_LINE('accion AFTER DELETE el suministro (' || :OLD.clvProv || ', ' || :OLD.clvPieza || ')'); -- raise_application_error( -20503, 'error despues de borrar tupla en Suministrar'); END tr_AftDelRow_Sum; TR_AFTDEL_SUM BEGIN DBMS_OUTPUT.PUT_LINE('accion AFTER DELETE en tabla Suministrar'); raise_application_error( -20504, 'error despues de borrar en Suministrar'); END tr_AftDel_Sum; TR_BEFDELROW_SUM BEGIN DBMS_OUTPUT.PUT_LINE('accion BEFORE DELETE el suministro (' || :OLD.clvProv || ', ' || :OLD.clvPieza || ')'); -- raise_application_error( -20502, 'error antes de borrar tupla en Suministrar'); END tr_BefDelRow_Sum; TR_BEFDEL_SUM BEGIN DBMS_OUTPUT.PUT_LINE('accion BEFORE DELETE en tabla Suministrar'); -- raise_application_error( -20501, 'error antes de iniciar el borrado en Suministrar'); END tr_BefDel_Sum; SQL> SQL> -- insertar algunas tuplas en suministrar SQL> INSERT INTO suministrar VALUES (1, 96); 1 fila creada. SQL> INSERT INTO suministrar VALUES (1, 97); 1 fila creada. SQL> INSERT INTO suministrar VALUES (3, 97); 1 fila creada. SQL> SQL> SELECT * FROM Suministrar ORDER BY clvPieza; CLVPROV CLVPIEZA ---------- ---------- 1 91 1 92 2 92 2 93 2 94 1 95 2 95 1 96 1 97 3 97 10 filas seleccionadas. SQL> SQL> -- eliminar una única tupla SQL> DELETE FROM suministrar WHERE clvPieza=96; accion BEFORE DELETE en tabla Suministrar accion BEFORE DELETE el suministro (1, 96) accion AFTER DELETE el suministro (1, 96) accion AFTER DELETE en tabla Suministrar DELETE FROM suministrar WHERE clvPieza=96 * ERROR en línea 1: ORA-20504: error despues de borrar en Suministrar ORA-06512: en "ASIGBD.TR_AFTDEL_SUM", línea 3 ORA-04088: error durante la ejecución del disparador 'ASIGBD.TR_AFTDEL_SUM' SQL> SQL> -- eliminar dos tuplas en una única sentencia SQL> DELETE FROM suministrar WHERE clvPieza=97; accion BEFORE DELETE en tabla Suministrar accion BEFORE DELETE el suministro (1, 97) accion AFTER DELETE el suministro (1, 97) accion BEFORE DELETE el suministro (3, 97) accion AFTER DELETE el suministro (3, 97) accion AFTER DELETE en tabla Suministrar DELETE FROM suministrar WHERE clvPieza=97 * ERROR en línea 1: ORA-20504: error despues de borrar en Suministrar ORA-06512: en "ASIGBD.TR_AFTDEL_SUM", línea 3 ORA-04088: error durante la ejecución del disparador 'ASIGBD.TR_AFTDEL_SUM' SQL> SQL> -- mostrar los suministros existentes SQL> SELECT * FROM Suministrar ORDER BY clvPieza; CLVPROV CLVPIEZA ---------- ---------- 1 91 1 92 2 92 2 93 2 94 1 95 2 95 1 96 1 97 3 97 10 filas seleccionadas. SQL> SQL> BEGIN 2 -- eliminar una única tupla 3 DELETE FROM suministrar WHERE clvPieza=96; 4 5 -- eliminar dos tuplas en una única sentencia 6 DELETE FROM suministrar WHERE clvPieza=97; 7 EXCEPTION 8 WHEN OTHERS THEN 9 DBMS_OUTPUT.PUT_LINE('ERROR '||sqlcode); 10 IF sqlcode = -20501 THEN DBMS_OUTPUT.PUT_LINE('ERROR antes de eliminar'); 11 ELSIF sqlcode = -20502 THEN DBMS_OUTPUT.PUT_LINE('ERROR antes de eliminar tupla'); 12 ELSIF sqlcode = -20503 THEN DBMS_OUTPUT.PUT_LINE('ERROR despues de eliminar tupla'); 13 ELSIF sqlcode = -20504 THEN DBMS_OUTPUT.PUT_LINE('ERROR despues de eliminar'); 14 END IF; 15 END; 16 / accion BEFORE DELETE en tabla Suministrar accion BEFORE DELETE el suministro (1, 96) accion AFTER DELETE el suministro (1, 96) accion AFTER DELETE en tabla Suministrar ERROR -20504 ERROR despues de eliminar Procedimiento PL/SQL terminado correctamente. SQL> SQL> -- mostrar los suministros existentes SQL> SELECT * FROM Suministrar ORDER BY clvPieza; CLVPROV CLVPIEZA ---------- ---------- 1 91 1 92 2 92 2 93 2 94 1 95 2 95 1 96 1 97 3 97 10 filas seleccionadas. SQL> SQL> CREATE OR REPLACE PROCEDURE test_TRIGGER AS 2 del_excepcion_BD EXCEPTION; 3 del_excepcion_BDR EXCEPTION; 4 del_excepcion_AD EXCEPTION; 5 del_excepcion_ADR EXCEPTION; 6 /* mapear los números de error a las excepciones*/ 7 PRAGMA EXCEPTION_INIT(del_excepcion_BD, -20501); 8 PRAGMA EXCEPTION_INIT(del_excepcion_BDR, -20502); 9 PRAGMA EXCEPTION_INIT(del_excepcion_AD, -20503); 10 PRAGMA EXCEPTION_INIT(del_excepcion_ADR, -20504); 11 BEGIN 12 -- eliminar una única tupla 13 DELETE FROM suministrar WHERE clvPieza=96; 14 15 -- eliminar dos tuplas en una única sentencia 16 DELETE FROM suministrar WHERE clvPieza=97; 17 EXCEPTION 18 WHEN del_excepcion_BD THEN DBMS_OUTPUT.PUT_LINE('ERROR antes de eliminar'); 19 WHEN del_excepcion_BDR THEN DBMS_OUTPUT.PUT_LINE('ERROR antes de eliminar tupla'); 20 WHEN del_excepcion_AD THEN DBMS_OUTPUT.PUT_LINE('ERROR despues de eliminar tupla'); 21 WHEN del_excepcion_ADR THEN DBMS_OUTPUT.PUT_LINE('ERROR despues de eliminar'); 22 WHEN OTHERS THEN NULL; 23 END; 24 . SQL> / Procedimiento creado. SQL> SHOW ERRORS PROCEDURE test_TRIGGER No hay errores. SQL> SQL> /* mostrar todos los procedures del usuario */ SQL> SELECT * FROM user_objects WHERE object_type='PROCEDURE'; nombre subobjeto OBJECT_ID id OBJECT_TYPE CREATED LAST_DDL TIMESTAMP STATUS T G S ------------------ --------- ---------- ---------- ------------------ -------- -------- ------------------- ------- - - - PONERPIEZA 2276959 PROCEDURE 10/04/14 05/05/15 2015-05-05:09:37:00 INVALID N N N PONERTOTPROVPIEZA 2276957 PROCEDURE 10/04/14 05/05/15 2015-05-05:10:47:20 INVALID N N N TEST_TRIGGER 2376789 PROCEDURE 11/05/15 11/05/15 2015-05-11:22:06:52 VALID N N N SQL> SQL> EXECUTE test_TRIGGER accion BEFORE DELETE en tabla Suministrar accion BEFORE DELETE el suministro (1, 96) accion AFTER DELETE el suministro (1, 96) accion AFTER DELETE en tabla Suministrar ERROR despues de eliminar Procedimiento PL/SQL terminado correctamente. SQL> SQL> -- mostrar los suministros existentes SQL> SELECT * FROM Suministrar ORDER BY clvPieza; CLVPROV CLVPIEZA ---------- ---------- 1 91 1 92 2 92 2 93 2 94 1 95 2 95 1 96 1 97 3 97 10 filas seleccionadas. SQL> SQL> -- FINAL DEL TEST. Eliminar de la BD los elementos añadidos. SQL> SQL> -- eliminar los disparadores creados en este ejemplo SQL> DROP TRIGGER tr_BefDel_Sum; Disparador borrado. SQL> DROP TRIGGER tr_BefDelRow_Sum; Disparador borrado. SQL> DROP TRIGGER tr_AftDel_Sum; Disparador borrado. SQL> DROP TRIGGER tr_AftDelRow_Sum; Disparador borrado. SQL> SQL> -- eliminar el procedimiento creado SQL> DROP PROCEDURE test_TRIGGER; Procedimiento borrado. SQL> SQL> -- eliminar las tuplas añadidas a suministrar (sólo para el caso en que los disparadores no lo hubieran permitido) SQL> SQL> DELETE FROM suministrar WHERE clvPieza=96; 1 fila suprimida. SQL> DELETE FROM suministrar WHERE clvPieza=97; 2 filas suprimidas. SQL> set echo off fin de la ejecucion de K:\BD_2015\pruebas_ORACLE\ej_clase\ej1_trigger\ej1b_trigger.sql inicio de sesión interactiva ASIGBD vicious.hendrix-oracle.cps.unizar.es fecha: 11/05/15 SQL> exit