SQL> @&1 SQL> /* @max_2_ProvPieza_v2.sql */ SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SET linesize 132 SQL> SET pagesize 300 SQL> column color format A32 SQL> column numProv format 999 SQL> SQL> /* Añadir a la tabla Pieza un atributo para el total de proveedores de la pieza SQL> */ SQL> ALTER TABLE Pieza ADD(numProv number); Tabla modificada. SQL> ALTER TABLE Pieza ADD CONSTRAINT numProv_NoNeg CHECK(numProv>=0); Tabla modificada. SQL> SQL> -- eliminar el atributo para añadirlo de otro modo SQL> ALTER TABLE Pieza DROP CONSTRAINT numProv_NoNeg; Tabla modificada. SQL> ALTER TABLE Pieza DROP(numProv); Tabla modificada. SQL> SQL> -- o directamente SQL> ALTER TABLE Pieza ADD(numProv number DEFAULT 0 CONSTRAINT numProv_NoNeg CHECK(numProv>=0)); Tabla modificada. SQL> SQL> /* Crear un procedimiento para calcular el total de proveedores de cada pieza SQL> */ SQL> CREATE OR REPLACE PROCEDURE set_numProvPieza_PR 2 IS 3 BEGIN 4 UPDATE Pieza P SET numProv=(SELECT count(*) FROM Suministrar WHERE clvPieza = P.clvPieza); 5 END set_numProvPieza_PR; 6 / Procedimiento creado. SQL> SHOW ERRORS PROCEDURE set_numProv_Pieza_PR No hay errores. SQL> SQL> column OBJECT_NAME heading "nombre" format a20; SQL> column DATA_OBJECT_ID heading "id" format a2; SQL> column SUBOBJECT_NAME heading "subobjeto" format a9; 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 -------------------- --------- ---------- ---------- ------------------ -------- -------- ------------------- ------- - - - PONERSUMINISTRO 2446212 PROCEDURE 13/05/16 15/05/16 2016-05-15:21:47:21 VALID N N N SET_NUMPROVPIEZA_PR 2446569 PROCEDURE 15/05/16 15/05/16 2016-05-15:21:48:04 VALID N N N SQL> SQL> /* asignar valor al total de proveedores de cada pieza SQL> */ SQL> EXECUTE set_numProvPieza_PR Procedimiento PL/SQL terminado correctamente. SQL> SQL> /* Crear un disparador para actualizar el nº de Proveedores por pieza SQL> */ SQL> CREATE OR REPLACE TRIGGER updateNumProvPieza_TR 2 AFTER INSERT OR DELETE OR UPDATE ON Suministrar 3 FOR EACH ROW 4 BEGIN 5 IF INSERTING THEN 6 UPDATE Pieza SET numProv=numProv+1 WHERE clvPieza = :NEW.clvPieza; 7 ELSIF DELETING THEN 8 UPDATE Pieza SET numProv=numProv-1 WHERE clvPieza = :OLD.clvPieza; 9 ELSIF UPDATING THEN 10 UPDATE Pieza SET numProv=numProv+1 WHERE clvPieza = :NEW.clvPieza; 11 UPDATE Pieza SET numProv=numProv-1 WHERE clvPieza = :OLD.clvPieza; 12 END IF; 13 END updateNumProvPieza_TR; 14 / Disparador creado. SQL> SHOW ERRORS TRIGGER updateNumProvPieza_TR No hay errores. SQL> SQL> /* Crear un disparador para impedir que el nº de Proveedores por pieza sea > 2 SQL> */ SQL> CREATE OR REPLACE TRIGGER max2ProvPieza_TR 2 BEFORE INSERT OR UPDATE ON Suministrar 3 FOR EACH ROW 4 DECLARE 5 totProv number; 6 BEGIN 7 SELECT numProv INTO totProv 8 FROM Pieza 9 WHERE :NEW.clvPieza = clvPieza; 10 DBMS_OUTPUT.PUT_LINE('la pieza ya tiene '||totProv||' proveedores'); 11 12 IF totProv > 1 13 THEN 14 raise_application_error( -20501, 'la pieza tiene el máximo de proveedores'); 15 END IF; 16 END max2ProvPieza_TR; 17 / Disparador creado. SQL> SHOW ERRORS TRIGGER max2ProvPieza_TR No hay errores. SQL> SQL> SELECT * FROM Pieza; CLVPIEZA NOMBPIEZA COLOR NUMPROV ---------- -------------------------------- -------------------------------- ------- 91 TUERCA ROJO 1 92 TUERCA VERDE 2 93 TORNILLO AZUL 1 94 TORNILLO ROJO 1 95 TUERCA AZUL 1 96 PALANCA 0 97 TUBO GRIS 0 77 paño amarillo 0 78 formón gris 0 9 filas seleccionadas. 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> -- Mostrar todos los disparadores y su estado SQL> SELECT TRIGGER_NAME, STATUS FROM USER_TRIGGERS; TRIGGER_NAME STATUS ------------------------------ -------- GANARETAPA ENABLED MAX2PROVPIEZA_TR ENABLED UPDATENUMPROVPIEZA_TR ENABLED UPDATE_MATRDXT ENABLED SQL> SQL> -- no se producirá error, pues suministrar ya no es mutante SQL> INSERT INTO suministrar VALUES (2, 95); la pieza ya tiene 1 proveedores 1 fila creada. SQL> INSERT INTO suministrar VALUES (2, 91); la pieza ya tiene 1 proveedores 1 fila creada. SQL> INSERT INTO suministrar VALUES (3, 92); la pieza ya tiene 2 proveedores INSERT INTO suministrar VALUES (3, 92) * ERROR en línea 1: ORA-20501: la pieza tiene el máximo de proveedores ORA-06512: en "ASIGBD.MAX2PROVPIEZA_TR", línea 11 ORA-04088: error durante la ejecución del disparador 'ASIGBD.MAX2PROVPIEZA_TR' SQL> INSERT INTO suministrar VALUES (3, 91); la pieza ya tiene 2 proveedores INSERT INTO suministrar VALUES (3, 91) * ERROR en línea 1: ORA-20501: la pieza tiene el máximo de proveedores ORA-06512: en "ASIGBD.MAX2PROVPIEZA_TR", línea 11 ORA-04088: error durante la ejecución del disparador 'ASIGBD.MAX2PROVPIEZA_TR' SQL> SQL> DELETE FROM suministrar WHERE clvProv = 3; 0 filas suprimidas. SQL> SQL> BEGIN 2 INSERT INTO suministrar SELECT 3, clvPieza FROM Suministrar WHERE clvProv=2; 3 EXCEPTION 4 WHEN OTHERS THEN 5 IF SQLCODE = -20501 THEN DBMS_OUTPUT.PUT_LINE('ERROR: max. 2 Prov/Pieza'); 6 END IF; 7 -- DBMS_OUTPUT.PUT_LINE('ERROR '||SQLERRM); 8 END; 9 / la pieza ya tiene 2 proveedores ERROR: max. 2 Prov/Pieza Procedimiento PL/SQL terminado correctamente. SQL> --------------------------------------------------------- SQL> SQL> -- FINAL DEL TEST. Eliminar de la BD los elementos añadidos. SQL> SQL> -- eliminar los disparadores y procedures creados en este ejemplo SQL> DROP TRIGGER max2ProvPieza_TR; Disparador borrado. SQL> DROP TRIGGER updateNumProvPieza_TR; Disparador borrado. SQL> DROP PROCEDURE set_numProvPieza_PR; Procedimiento borrado. SQL> SQL> -- eliminar las tuplas añadidas SQL> DELETE FROM suministrar WHERE (clvProv, clvPieza) IN ((2,91), (2,95)); 2 filas suprimidas. SQL> DELETE FROM suministrar WHERE clvProv = 3; 0 filas suprimidas. SQL> SQL> -- eliminar el atributo numProv añadido a la tabla Pieza SQL> ALTER TABLE Pieza DROP COLUMN numProv; Tabla modificada. SQL> exit