/* @historico_proveedor.sql */
/* Este ejemplo pretende ilustrar la utilización de disparadores para auditar determinadas operaciones efectuadas en la BD.
   Para ello se crea un disparador que se activa con cada cambio en las tuplas de proveedor y genera una entrada en una tabla
   con el histórico de cambios.
   S.V. Univ. Zaragoza, 10 mayo 2015
*/
SET ECHO ON
SET SERVEROUTPUT ON
SET LINESIZE 132
SET PAGESIZE 200

/* Crear la tabla para el histórico de operaciones en Proveedor
*/
CREATE TABLE hist_Proveedor      (
  idOp      number(5) CONSTRAINT idOp_PK PRIMARY KEY,
  clvProv   number(9)     NOT NULL,
  nombProv  char(32)      NOT NULL,
  fecha     date          NOT NULL,
  empleado  varchar2(255) NOT NULL,
  tpOp      char(2)       NOT NULL);

/* Crear una secuencia para generar el idOp de las operaciones
*/
CREATE SEQUENCE idHistOpSQ;

/* Crear un disparador para generar las tuplas de las op. realizadas
*/
CREATE OR REPLACE TRIGGER histProveedor_TR
AFTER INSERT OR UPDATE OR DELETE ON Proveedor
FOR EACH ROW
DECLARE
  idProv   Proveedor.clvProv%TYPE;
  nombre   Proveedor.nombProv%TYPE;
  Operac   hist_Proveedor.tpOp%TYPE;
BEGIN
  IF     INSERTING THEN Operac := 'I';  idProv := :NEW.clvProv;  nombre := :NEW.nombProv;
   ELSIF DELETING  THEN Operac := 'D';  idProv := :OLD.clvProv;  nombre := :OLD.nombProv;
   ELSIF UPDATING  THEN Operac := 'U+'; idProv := :NEW.clvProv;  nombre := :NEW.nombProv;
    INSERT INTO hist_Proveedor VALUES (idHistOpSQ.NEXTVAL, :OLD.clvProv, :OLD.nombProv, SYSDATE, USER, 'U-');
  END IF;

  INSERT INTO hist_Proveedor VALUES (idHistOpSQ.NEXTVAL, idProv, nombre, SYSDATE, USER, Operac);
END histPiezas_TR;
/
SHOW ERRORS TRIGGER histProveedor_TR;

column empleado format A20
column nombProv format A32
column idOp format 999

DELETE FROM suministrar;
DELETE FROM proveedor;
DELETE FROM hist_Proveedor;

INSERT INTO Proveedor VALUES (1,     'PEREZ');
SELECT * FROM proveedor;
SELECT * FROM hist_Proveedor;
UPDATE Proveedor SET clvProv=2, nombProv='LOPEZ' WHERE clvProv=1;
INSERT INTO Proveedor VALUES (1,     'PEREZ');
INSERT INTO Proveedor VALUES (4,     'MARIANO');
INSERT INTO Proveedor VALUES (3,     'MARTINEZ');
DELETE FROM Proveedor WHERE clvProv=4;
SELECT * FROM proveedor;
SELECT * FROM hist_Proveedor;

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

DROP TRIGGER histProveedor_TR;
DROP TABLE hist_Proveedor;

DROP SEQUENCE idHistOpSQ;

INSERT INTO suministrar VALUES (1, 91);
INSERT INTO suministrar VALUES (1, 92);
INSERT INTO suministrar VALUES (2, 92);
INSERT INTO suministrar VALUES (2, 93);
INSERT INTO suministrar VALUES (2, 94);
INSERT INTO suministrar VALUES (1, 95);