SQL> @&1 SQL> /* @max_2_provPieza_v1.sql */ SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SQL> /* Crear un disparador para impedir que el nº de Proveedores por pieza sea > 2 SQL> */ SQL> CREATE OR REPLACE TRIGGER tst_maxProv_TR 2 BEFORE INSERT ON Suministrar 3 FOR EACH ROW 4 DECLARE 5 totProv number; 6 BEGIN 7 SELECT count(*) INTO totProv 8 FROM Suministrar 9 WHERE clvPieza = :NEW.clvPieza; 10 DBMS_OUTPUT.PUT_LINE('hay '||totProv||' proveedores de la pieza '||:NEW.clvPieza); 11 12 IF totProv > 1 THEN 13 raise_application_error( -20501, 'la pieza tiene el máximo de proveedores'); 14 END IF; 15 END tst_maxProv_TR; 16 / Disparador creado. SQL> SHOW ERRORS TRIGGER tst_maxProv_TR No hay errores. SQL> SQL> SELECT * FROM Suministrar ORDER BY clvProv, clvPieza; CLVPROV CLVPIEZA ---------- ---------- 1 91 1 92 1 95 2 92 2 93 2 94 6 filas seleccionadas. SQL> SQL> -- no se producirá error, pues suministrar no es mutante ¡en este caso particular! (una tupla) SQL> INSERT INTO suministrar VALUES (2, 95); hay 1 proveedores de la pieza 95 1 fila creada. SQL> SQL> -- el disparador aborta la transacción normalmente (suministrar no es mutante en este caso) SQL> INSERT INTO suministrar VALUES (3, 95); hay 2 proveedores de la pieza 95 INSERT INTO suministrar VALUES (3, 95) * ERROR en línea 1: ORA-20501: la pieza tiene el máximo de proveedores ORA-06512: en "ASIGBD.TST_MAXPROV_TR", línea 10 ORA-04088: error durante la ejecución del disparador 'ASIGBD.TST_MAXPROV_TR' SQL> SQL> -- SI se producirá error, pues suministrar es mutante (se intenta insertar más de una tupla) SQL> INSERT INTO suministrar SELECT 3, clvPieza FROM Suministrar WHERE clvProv=2; INSERT INTO suministrar SELECT 3, clvPieza FROM Suministrar WHERE clvProv=2 * ERROR en línea 1: ORA-04091: la tabla ASIGBD.SUMINISTRAR está mutando, puede que el disparador/la función no puedan verla ORA-06512: en "ASIGBD.TST_MAXPROV_TR", línea 4 ORA-04088: error durante la ejecución del disparador 'ASIGBD.TST_MAXPROV_TR' SQL> SQL> -- Si el disparador se define como AFTER, la tabla siempre será mutante SQL> /* Crear un disparador para impedir que el nº de Proveedores por pieza sea > 2 SQL> */ SQL> CREATE OR REPLACE TRIGGER tst_maxProv_TR 2 AFTER INSERT ON Suministrar 3 FOR EACH ROW 4 DECLARE 5 totProv number; 6 BEGIN 7 SELECT count(*) INTO totProv 8 FROM Suministrar 9 WHERE :NEW.clvPieza = clvPieza; 10 DBMS_OUTPUT.PUT_LINE('hay '||totProv||' proveedores de la pieza '||:NEW.clvPieza); 11 12 IF totProv > 1 THEN 13 raise_application_error( -20501, 'la pieza tiene el máximo de proveedores'); 14 END IF; 15 END tst_maxProv_TR; 16 / Disparador creado. SQL> SHOW ERRORS TRIGGER tst_maxProv_TR No hay errores. SQL> SQL> DELETE FROM suministrar WHERE (clvProv, clvPieza) IN ((2,95)); 1 fila suprimida. SQL> SQL> -- se producirá error, pues suministrar es mutante dentro del disparador (se ha cambiado) SQL> INSERT INTO suministrar VALUES (2, 95); INSERT INTO suministrar VALUES (2, 95) * ERROR en línea 1: ORA-04091: la tabla ASIGBD.SUMINISTRAR está mutando, puede que el disparador/la función no puedan verla ORA-06512: en "ASIGBD.TST_MAXPROV_TR", línea 4 ORA-04088: error durante la ejecución del disparador 'ASIGBD.TST_MAXPROV_TR' SQL> SQL> DROP TRIGGER tst_maxProv_TR; Disparador borrado. SQL> SQL> /* Un modo bastante sencillo y general de resolver el problema de las tablas mutantes consiste en SQL> implementar el tratamiento en base a dos disparadores: uno a nivel de tupla en el que simplemente SQL> se guardan las tuplas modificadas por el evento en una estructura auxiliar (por ejemplo una tabla), SQL> y otro a nivel de sentencia en el que se tratan las tuplas guardadas en la estructura auxiliar. SQL> Como este disparador funciona a nivel de sentencia, la tabla ya no es mutante y, por tanto, en el SQL> tratamiento se pueden realizar consultas sobre la tabla modificada por el evento. SQL> Para recorrer las tuplas guardadas se puede utilizar un cursor. SQL> */ SQL> /* como ejemplo, se va a resolver el problema utilizando esta técnica */ SQL> /* Crear una tabla auxiliar para guardar las tuplas modificadas por el evento SQL> */ SQL> CREATE TABLE SumPend ( 2 clvProv number(9), 3 clvPieza number(9) 4 ); Tabla creada. SQL> SQL> /* Crear un disparador para guardar en la tabla auxiliar los cambios en Suministrar SQL> */ SQL> CREATE OR REPLACE TRIGGER tst_maxProv_TR 2 BEFORE INSERT OR UPDATE ON Suministrar 3 FOR EACH ROW 4 BEGIN 5 INSERT INTO SumPend VALUES(:NEW.clvProv, :NEW.clvPieza); 6 DBMS_OUTPUT.PUT_LINE('intento de añadir suministro ' || :NEW.clvProv || ' ' || :NEW.clvPieza); 7 END tst_maxProv_TR; 8 / Disparador creado. SQL> SHOW ERRORS TRIGGER tst_maxProv_TR No hay errores. SQL> SQL> /* Crear un disparador a nivel de sentencia para procesar los cambios guardados en la tabla auxiliar SQL> */ SQL> CREATE OR REPLACE TRIGGER nuevosSuministros_TR 2 AFTER INSERT OR UPDATE ON Suministrar 3 DECLARE 4 laPieza Suministrar.clvPieza%TYPE; 5 elProveedor Suministrar.clvProv%TYPE; 6 totalProv number; 7 CURSOR selCambio IS SELECT clvPieza, clvProv FROM SumPend; 8 BEGIN 9 OPEN selCambio; 10 LOOP 11 FETCH selCambio INTO laPieza, elProveedor; 12 EXIT WHEN selCambio%NOTFOUND; 13 SELECT count(*) INTO totalProv 14 FROM Suministrar S 15 WHERE S.clvPieza = laPieza; 16 IF totalProv > 2 THEN 17 DBMS_OUTPUT.PUT_LINE('la pieza '|| laPieza || ' ya tiene '|| (totalProv-1) || ' proveedores'); 18 raise_application_error( -20501, 'número máximo de proveedores alcanzado'); 19 END IF; 20 END LOOP; 21 DELETE FROM SumPend; 22 END tst_maxProv_TR; 23 / Disparador creado. SQL> SHOW ERRORS TRIGGER nuevosSuministros_TR No hay errores. SQL> SQL> -- no se producirá error SQL> INSERT INTO suministrar VALUES (2, 95); intento de añadir suministro 2 95 1 fila creada. SQL> SQL> -- el disparador aborta la transacción normalmente SQL> INSERT INTO suministrar VALUES (3, 95); intento de añadir suministro 3 95 la pieza 95 ya tiene 2 proveedores INSERT INTO suministrar VALUES (3, 95) * ERROR en línea 1: ORA-20501: número máximo de proveedores alcanzado ORA-06512: en "ASIGBD.NUEVOSSUMINISTROS_TR", línea 16 ORA-04088: error durante la ejecución del disparador 'ASIGBD.NUEVOSSUMINISTROS_TR' SQL> INSERT INTO suministrar SELECT 3, clvPieza FROM Suministrar WHERE clvProv=2; intento de añadir suministro 3 92 intento de añadir suministro 3 93 intento de añadir suministro 3 94 intento de añadir suministro 3 95 la pieza 92 ya tiene 2 proveedores INSERT INTO suministrar SELECT 3, clvPieza FROM Suministrar WHERE clvProv=2 * ERROR en línea 1: ORA-20501: número máximo de proveedores alcanzado ORA-06512: en "ASIGBD.NUEVOSSUMINISTROS_TR", línea 16 ORA-04088: error durante la ejecución del disparador 'ASIGBD.NUEVOSSUMINISTROS_TR' SQL> SQL> -- dejar la tabla suministrar como estaba antes de ejecutar el ejemplo SQL> DELETE FROM suministrar WHERE (clvProv, clvPieza) IN ((2,95)); 1 fila suprimida. SQL> DELETE FROM suministrar WHERE clvProv = 3; 0 filas suprimidas. SQL> SQL> SELECT * FROM Suministrar ORDER BY clvProv, clvPieza; CLVPROV CLVPIEZA ---------- ---------- 1 91 1 92 1 95 2 92 2 93 2 94 6 filas seleccionadas. SQL> SQL> -- eliminar la tabla auxiliar SQL> DROP TABLE SumPend; Tabla borrada. SQL> SQL> -- eliminar los disparadores creados SQL> DROP TRIGGER nuevosSuministros_TR; Disparador borrado. SQL> DROP TRIGGER tst_maxProv_TR; Disparador borrado. SQL> exit