/* @ej1b_trigger.sql */
/* Este ejemplo pretende ilustrar las diferencias entre los diferentes modos de especificar
   los eventos en disparadores (a nivel de sentencia o a nivel de tupla y BEFORE o AFTER) y
   mostrar el orden en que se ejecutan.  Para ello se ha implementado un disparador de cada
   tipo que muestran un mensaje cuando se ejecuta una sentencia de eliminar un suministro y
   opcionalmente se genera una excepción (por defecto  sólo se ha activado en el disparador
   de después de ejecutar la sentencia).   Pretende ilustrar algunos modos de gestionar las
   excepciones.
   S.V. Univ. Zaragoza, 10 mayo 2015
*/
SET ECHO ON
SET SERVEROUTPUT ON
SET linesize 255
SET pagesize 100

-- modificar el ancho para columnas de tipo LONG (p.e. el cuerpo de un trigger)
SET LONG 800

-- mostrar las tuplas de suministrar
SELECT * FROM Suministrar;

/* Crear un disparador para ilustrar BEFORE a nivel de sentencia
*/
CREATE OR REPLACE TRIGGER tr_BefDel_Sum
BEFORE DELETE ON Suministrar
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;
/
SHOW ERRORS TRIGGER tr_BefDel_Sum

/* Crear un disparador para ilustrar BEFORE a nivel de tupla
*/
CREATE OR REPLACE TRIGGER tr_BefDelRow_Sum
BEFORE DELETE ON Suministrar
FOR EACH ROW
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;
/
SHOW ERRORS TRIGGER tr_BefDelRow_Sum

/* Crear un disparador para ilustrar AFTER a nivel de tupla
*/
CREATE OR REPLACE TRIGGER tr_AftDelRow_Sum
AFTER DELETE ON Suministrar
FOR EACH ROW
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;
/
SHOW ERRORS TRIGGER tr_AftDelRow_Sum

/* Crear un disparador para ilustrar AFTER a nivel de sentencia
*/
CREATE OR REPLACE TRIGGER tr_AftDel_Sum
AFTER DELETE ON Suministrar
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;
/
SHOW ERRORS TRIGGER tr_AftDel_Sum

column TRIGGER_NAME      heading "nombre"    format a20;
column TRIGGER_TYPE      heading "tipo"      format a20;
column TRIGGERING_EVENT  heading "evento"    format a60;
column TRIGGER_BODY      heading "accion"    format a115;

/* mostrar todos los disparadores del usuario */
-- select * from USER_TRIGGERS;
SELECT TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT FROM USER_TRIGGERS;

column OBJECT_NAME       heading "nombre"    format a18;
column DATA_OBJECT_ID    heading "id"        format a2;
column SUBOBJECT_NAME    heading "subobjeto" format a9;

/* mostrar todos los disparadores del usuario */
SELECT * FROM user_objects WHERE object_type='TRIGGER';

/* mostrar todos los disparadores sobre la tabla Suministrar */
SELECT TRIGGER_NAME, TABLE_OWNER FROM USER_TRIGGERS WHERE TABLE_NAME = 'SUMINISTRAR';

/* mostrar el cuerpo de todos los disparadores sobre la tabla Suministrar */
SELECT TRIGGER_NAME, TRIGGER_BODY FROM USER_TRIGGERS WHERE TABLE_NAME = 'SUMINISTRAR';

-- insertar algunas tuplas en suministrar
INSERT INTO suministrar VALUES (1, 96);
INSERT INTO suministrar VALUES (1, 97);
INSERT INTO suministrar VALUES (3, 97);

SELECT * FROM Suministrar ORDER BY clvPieza;

-- eliminar una única tupla
DELETE FROM suministrar WHERE clvPieza=96;

-- eliminar dos tuplas en una única sentencia
DELETE FROM suministrar WHERE clvPieza=97;

-- mostrar los suministros existentes
SELECT * FROM Suministrar ORDER BY clvPieza;

BEGIN
-- eliminar una única tupla
  DELETE FROM suministrar WHERE clvPieza=96;

-- eliminar dos tuplas en una única sentencia
  DELETE FROM suministrar WHERE clvPieza=97;
EXCEPTION
   WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('ERROR '||sqlcode);
   IF    sqlcode = -20501 THEN DBMS_OUTPUT.PUT_LINE('ERROR antes   de eliminar');
   ELSIF sqlcode = -20502 THEN DBMS_OUTPUT.PUT_LINE('ERROR antes   de eliminar tupla');
   ELSIF sqlcode = -20503 THEN DBMS_OUTPUT.PUT_LINE('ERROR despues de eliminar tupla');
   ELSIF sqlcode = -20504 THEN DBMS_OUTPUT.PUT_LINE('ERROR despues de eliminar');
   END IF;
END;
/

-- mostrar los suministros existentes
SELECT * FROM Suministrar ORDER BY clvPieza;

CREATE OR REPLACE PROCEDURE test_TRIGGER AS
  del_excepcion_BD EXCEPTION;
  del_excepcion_BDR EXCEPTION;
  del_excepcion_AD EXCEPTION;
  del_excepcion_ADR EXCEPTION;
  /* mapear los números de error a las excepciones*/
  PRAGMA EXCEPTION_INIT(del_excepcion_BD,  -20501);
  PRAGMA EXCEPTION_INIT(del_excepcion_BDR, -20502);
  PRAGMA EXCEPTION_INIT(del_excepcion_AD,  -20503);
  PRAGMA EXCEPTION_INIT(del_excepcion_ADR, -20504);
BEGIN
-- eliminar una única tupla
  DELETE FROM suministrar WHERE clvPieza=96;

-- eliminar dos tuplas en una única sentencia
  DELETE FROM suministrar WHERE clvPieza=97;
EXCEPTION
  WHEN del_excepcion_BD  THEN DBMS_OUTPUT.PUT_LINE('ERROR antes de eliminar');
  WHEN del_excepcion_BDR THEN DBMS_OUTPUT.PUT_LINE('ERROR antes de eliminar tupla');
  WHEN del_excepcion_AD  THEN DBMS_OUTPUT.PUT_LINE('ERROR despues de eliminar tupla');
  WHEN del_excepcion_ADR THEN DBMS_OUTPUT.PUT_LINE('ERROR despues de eliminar');
  WHEN OTHERS THEN NULL;
END;
.
/
SHOW ERRORS PROCEDURE test_TRIGGER

/* mostrar todos los procedures del usuario */
SELECT * FROM user_objects WHERE object_type='PROCEDURE';

EXECUTE test_TRIGGER

-- mostrar los suministros existentes
SELECT * FROM Suministrar ORDER BY clvPieza;

-- FINAL DEL TEST.  Eliminar de la BD los elementos añadidos.

-- eliminar los disparadores creados en este ejemplo
DROP TRIGGER tr_BefDel_Sum;
DROP TRIGGER tr_BefDelRow_Sum;
DROP TRIGGER tr_AftDel_Sum;
DROP TRIGGER tr_AftDelRow_Sum;

-- eliminar el procedimiento creado
DROP PROCEDURE test_TRIGGER;

-- eliminar las tuplas añadidas a suministrar (sólo para el caso en que los disparadores no lo hubieran permitido)

DELETE FROM suministrar WHERE clvPieza=96;
DELETE FROM suministrar WHERE clvPieza=97;