5.1. Disparadores

Un disparador (o trigger) es un tipo especial de procedimiento almacenado asociado a una tabla que se ejecuta al realizar una operación 'básica' (INSERT, DELETE o UPDATE) sobre ésta. La operación básica que despierta al trigger es conocida como sentencia disparadora.

Los triggers constituyen una potente herramienta para mantener la integridad de la base de datos, ya que pueden llevar a cabo cualquier acción que sea necesaria para el mantenimiento de dicha integridad.

Los triggers pueden llamar a otros procedimientos y disparar otros triggers, pero no admiten parámetros y no pueden ser invocados desde otros procedimientos.

La sintaxis de un disparador Oracle es

DELIMITER |
  CREATE  TRIGGER nombre
    {BEFORE | AFTER | INSTEAD OF} // Temporalidad del Evento
    {INSERT | DELETE | UPDATE [OF <lista de columnas>]} ON <tabla>
    [FOR EACH ROW]  //Granularidad
    [WHEN condición]
    [DECLARE //Declaración de variables
      . . .]
  BEGIN
    cuerpo del trigger
    [EXCEPTION
      . . .]
  END
  |
  DELIMITER ;

Los disparadores se activan al crearlos.

Eliminar un disparador:

DROP TRIGGER nombre_disparador;

Disparadores del esquema

SELECT trigger_type, triggering_event, table_name, referencing_names, trigger_body
FROM user_triggers;

Activar/ Desactivar dispadores

Existen dos opciones.

La ejecución del disparador puede ser antes (before) o después (after) de llevar a cabo la sentencia disparadora. Es posible especificar condiciones adicionales para la ejecución del disparador (restrictores).

Dado que una sentencia disparadora puede afectar una o más filas de una tabla, es necesario especificar si se quiere que el disparador se ejecute para cada una de las filas afectadas o para el bloque en general.

Para diseñar un disparador hay que cumplir dos requisitos:

Registros old y new.

Estas variables se utilizan del mismo modo que cualquier otra variable, con la salvedad de que no es necesario declararlas,son de tipo %ROWTYPE y contienen una copia del registro antes (OLD) y después (NEW) de la acción SQL (INSERT, UPDATE, DELETE) que ha ejecutado el trigger. Utilizando esta variable podemos acceder a los datos que se están insertando, actualizando o borrando.

De uso exclusivo en los disparadores de nivel de fila, si se intenta hacer referencia a cualquiera de los dos dentro de otro tipo de disparador, se obtendrá un error de compilación.

La siguiente tabla resume los valores regresados por estos seudoregistros en diferentes eventos

Evento Seudoregistros
:OLD :NEW
INSERT NULL Nuevos valores
DELETE Valores almacenados NULL
UPDATE Valores almacenados Nuevos valores

Ejemplo La siguiente estructura representa una tabla denominada amigos. De la misma tenemos una copia denominada amigosCopia. Desarrollaremos dos disparadores uno de ellos será AFTER INSERT y el otro UPDATE INSERT

Estructuras y secuencia

 CREATE TABLE amigos (
   idAmigo NUMBER(4) PRIMARY KEY,
   nombre  CHAR(35) NOT NULL,
   celular NUMBER(13) NOT NULL
 );
 
  CREATE TABLE amigosCopia (
   idAmigo NUMBER(4) PRIMARY KEY,
   nombre  CHAR(35) NOT NULL,
   celular NUMBER(13) NOT NULL
 );

 CREATE SEQUENCE idAmigo INCREMENT BY 1;

Note: una secuencia en Oracle es la forma de utilizar valores de autoincremento.

Disparadores

 CREATE OR REPLACE TRIGGER afterAmigos 
  AFTER INSERT ON amigos
  REFERENCING NEW AS NEW
  FOR EACH ROW
  BEGIN
    INSERT INTO amigosCopia VALUES (:NEW.idAmigo,:NEW.nombre, :NEW.celular);
	DBMS_OUTPUT.PUT_LINE('Registro duplicado en amigosCopia.');
  END;
  /

Datos de prueba

INSERT INTO amigos VALUES (idAmigo.NEXTVAL, 'RAUL TRUJILLO JIMENEZ', 9345678);
INSERT INTO amigos VALUES (idAmigo.NEXTVAL, 'MARTIN ARIAS PEÑA', 9344678);
CREATE OR REPLACE TRIGGER afterUpdateAmigos 
 AFTER UPDATE OF nombre, celular ON amigos
   REFERENCING NEW AS NEW
  FOR EACH ROW
  BEGIN
    UPDATE amigosCopia SET nombre = :NEW.nombre, celular = :NEW.celular WHERE idAmigo = :OLD.idAmigo;
	DBMS_OUTPUT.PUT_LINE('Registro modificado en amigosCopia.');
  END;

Datos de prueba

UPDATE amigos SET celular = '2299397845' WHERE idAmigo = 1;

La sentencia anterior modifica solo el campo celular. Sin embargo el disparador funciona. Observe el resultado

Manejo de excepciones

Para hacer que un trigger ejecute un ROLLBACK de la transacción que tiene activa y teniendo en cuenta que en las sentencias que componen el cuerpo de un trigger no puede haber este tipo de sentencias (rollback, commit,…) hay que ejecutar 'error / excepcion' mediante la sentencia raise_application_error cuya sintaxis es:

RAISE_APPLICATION_ERROR(num_error,'mensaje');

Donde num_error en el rango: [-20000 y -20999]

Ejemplo: Retomando el ejemplo anterior, desarrollamos una excepción en nuestra tabla amigos. En la cual no admitimos como amiga a 'Isabel Mebarak Ripoll'

.
CREATE OR REPLACE TRIGGER noAmigos
BEFORE INSERT ON amigos
REFERENCING NEW AS NEW
  FOR EACH ROW
BEGIN
  IF :NEW.nombre = 'Isabel Mebarak Ripoll' THEN
    RAISE_APPLICATION_ERROR(-20000,'Ella no es mi amiga es mi sueño imposible');
  END IF;
END;
/

Ejemplo: Suponga que se tienen las siguientes relaciones, asociadas a la concesión de préstamos en un banco:

Para fines de este ejemplo desarrollamos dos disparadores.

El primer disparador crea el primer pago al otorgarse el crédito, y le aplica una fecha de vencimiento de 30 días, más el interés correspondiente.

CREATE OR REPLACE TRIGGER primerPago
AFTER INSERT ON prestamo
REFERENCING NEW AS NEW
FOR EACH ROW
DECLARE
   intereses NUMBER;
   fPago DATE;
BEGIN
   intereses:= :NEW.monto * :NEW.interes;
   fPago    := :NEW.fechaPrestamo + 30;

   -- Insertar primer pago
   INSERT INTO pagos ( nPago, nPrestamo, fechaVencimiento, saldo, intereses)
   VALUES ( nPago.NEXTVAL, :new.nPrestamo, fPago, :new.monto, intereses);
   dbms_output.put('Fecha Pago: ' || fPago);
  dbms_output.put('Pago mínimo: ' || TO_CHAR(intereses,'999,999,999.99'));
END;
/

El segundo disparador verifica que el pago se realice dentro de las fechas pactadas

CREATE OR REPLACE TRIGGER Abonos
BEFORE UPDATE OF fechaPago ON pagos
FOR EACH ROW
WHEN (new.fechaPago > old.fechaVencimiento)
BEGIN
 RAISE_APPLICATION_ERROR(-20000, 'Cuota ' || TO_CHAR(:old.nPago) || ' del prestamo ' || TO_CHAR(:old.nPrestamo) || 'vencida. Por favor, dirigirse a la gerenci
a.');
END;
/

Datos de prueba

INSERT INTO deudor VALUES ('987654321234','Juan Carlos Padilla Salas','En su casa','9394334');
INSERT INTO prestamo VALUES(nPrestamo.NEXTVAL,'987654321234','28/07/16',50000.00,50000.00,0.02);

Disparadores de sustitución

Podemos crear triggers que no se ejecutan antes ni después de una instrucción sino en lugar de (instead of).

Solo podemos utilizar estos triggers si están asociados a vistas, además actúan siempre a nivel de fila.

Ejemplo: Eliminar un prestamo no aceptado por el el cliente

CREATE VIEW prestamoPago AS
  SELECT M.nPrestamo, nPago
  FROM prestamo M, pagos P
  WHERE M.nPrestamo = P.nPrestamo;

Creamos el disparador y lo probamos

CREATE  OR REPLACE TRIGGER borrarPrestamo
    instead of delete on prestamoPago
    for each row
    DECLARE n NUMBER;
BEGIN
  SELECT COUNT(*)INTO n FROM prestamoPago;
  IF n = 1 THEN
    DELETE FROM pagos    WHERE nPago = :OLD.nPago;  
   DELETE FROM prestamo WHERE nPrestamo  = :OLD.nPrestamo;
  END IF; 
END;
/

DELETE FROM prestamoPago WHERE nPrestamo = 1;

Predicados condicionales

Cuando se crea un trigger para más de una operación DML, se puede utilizar un predicado condicional en las sentencias que componen el trigger que indique que tipo de operación o sentencia ha disparado el trigger. Estos predicados condicionales son los siguientes:

Ejemplo: Con el tiempo aprendes a convivir con unos y a sobrevivir sin otros. Aprovechemos el juego de moda para aprender, Triggers

CREATE TABLE pokemon (
   idPokemon  INTEGER PRIMARY KEY,
   nombre     CHAR(30) NOT NULL,
   generacion INTEGER NOT NULL);
   
   CREATE SEQUENCE idPokemon INCREMENT BY 1;

Datos

INSERT INTO pokemon VALUES(idPokemon.NEXTVAL,'Articuno',1);
INSERT INTO pokemon VALUES(idPokemon.NEXTVAL,'Zapdos',1);
INSERT INTO pokemon VALUES(idPokemon.NEXTVAL,'Moltres',1);
INSERT INTO pokemon VALUES(idPokemon.NEXTVAL,'Mewtwo',1);
INSERT INTO pokemon VALUES(idPokemon.NEXTVAL,'Mew',1);
INSERT INTO pokemon VALUES(idPokemon.NEXTVAL,'Raikou',2);
INSERT INTO pokemon VALUES(idPokemon.NEXTVAL,'Entei',2);
INSERT INTO pokemon VALUES(idPokemon.NEXTVAL,'Suicune',2);
INSERT INTO pokemon VALUES(idPokemon.NEXTVAL,'Lugia',2);
INSERT INTO pokemon VALUES(idPokemon.NEXTVAL,'Ho-Oh ',2);
INSERT INTO pokemon VALUES(idPokemon.NEXTVAL,'Celebi',2);
INSERT INTO pokemon VALUES(idPokemon.NEXTVAL,'Regirock',3);
INSERT INTO pokemon VALUES(idPokemon.NEXTVAL,'Regice',3);
INSERT INTO pokemon VALUES(idPokemon.NEXTVAL,'Registeel',3);
INSERT INTO pokemon VALUES(idPokemon.NEXTVAL,'Latias',3);
INSERT INTO pokemon VALUES(idPokemon.NEXTVAL,'Uxie',4);
INSERT INTO pokemon VALUES(idPokemon.NEXTVAL,'Mesprit',4);
INSERT INTO pokemon VALUES(idPokemon.NEXTVAL,'Azelf',4);

Disparador

CREATE  OR REPLACE TRIGGER auditoria 
  BEFORE INSERT OR DELETE OR UPDATE ON pokemon
  FOR EACH ROW
  DECLARE
      operacion CHAR(1);
  BEGIN
    /* Usemos 'I' para INSERT, 'D' para DELETE, y 'U' para UPDATE. */
    IF INSERTING THEN
       operacion := 'I';
    ELSIF UPDATING THEN
      operacion := 'U';
    ELSE
      operacion := 'D';
    END IF;
 
    DBMS_OUTPUT.put_line(operacion ||' '|| USER ||' ' ||SYSDATE);   
  END;
  /

Ejecución

SET SERVEROUT ON;
UPDATE pokemon SET generacion = 5 WHERE generacion = 4 ;
DELETE pokemon WHERE generacion = 5;

SET SERVEROUT ON asegura que el disparador muestre resultados mediante DBMS_OUTPUT.put_line

Ejemplo: Las cuentas actúan como el elemento de mayor detalle con el que podemos trabajar en contabilidad recogiéndose en ellas la actividad desarrollada con un cliente, proveedor, cuenta bancaria, elemento de activo, etc.

El debe y el haber

Las cuentas contables tienen dos zonas para anotar los movimientos producidos, debe y haber, de tal modo que dependiendo del tipo de movimiento producido en la cuenta, la anotación la realizaremos al debe o al haber.

Así, si representamos gráficamente la cuenta tendrá la siguiente apariencia:

Los detalles contables escapan del contenido del curso pero puede consultar el siguiente link o cualquiera de los numerosos ejemplares de su biblioteca. Sin más el código que representa este ejemplo es el siguiente

CREATE TABLE apuntes( 
  asiento INT(8) DEFAULT 0, 
  linea   SMALLINT(5) DEFAULT 0, 
  fecha   DATE,  
  texto   CHAR(40), 
  cuenta  CHAR(10),
  debe    DOUBLE(10,2) DEFAULT 0, 
  haber   DOUBLE(10,2) DEFAULT 0, 
  PRIMARY KEY (asiento, linea), KEY K2(cuenta, fecha)) 
ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

DROP TABLE IF EXISTS saldo;
CREATE TABLE saldo (
  cuenta CHAR(10) NOT NULL DEFAULT '', 
  ano    SMALLINT(4) DEFAULT 0, 
  mes    TINYINT(2) DEFAULT 0, 
  debe   DOUBLE(10,2) DEFAULT 0, 
  haber  DOUBLE(10,2) DEFAULT 0, 
  PRIMARY KEY (cuenta, ano, mes) ) 
ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

Vamos a proceder a crear los Triggers, para la tabla de apuntes.

Trigger de inserción de registros.

Remarcar que los saldos se actualizan después de entrar un registro en la tabla de apuntes (AFTER INSERT). Muy interesante es la orden: INSERT INTO ... ON DUPLICATE KEY UPDATE. Esta sentencia inserta un registro en la tabla de saldos, y si este existiera, actualiza solamente las columnas debe y haber. Por eso hemos definido claves primarias (PRIMARY KEY) en las tablas, requisito indispensable para que esta sentencia funcione.

DELIMITER //
CREATE TRIGGER apuntesI  AFTER INSERT ON apuntes
  FOR EACH ROW 
BEGIN 
  INSERT INTO saldo SET 
    saldo.cuenta = NEW.cuenta, 
    saldo.ano = YEAR(NEW.fecha), 
    saldo.MES = MONTH(NEW.fecha), 
    saldo.debe = NEW.debe, 
    saldo.haber=NEW.haber
    ON DUPLICATE KEY UPDATE  
       saldo.debe = saldo.debe + NEW.debe,
      saldo.haber = saldo.haber + NEW.haber;
END;//
DELIMITER ;

TRIGGER update

DELIMITER //
CREATE TRIGGER apuntesU  AFTER UPDATE ON apuntes
  FOR EACH ROW 
BEGIN 
  INSERT INTO saldo SET 
    saldo.cuenta = OLD.cuenta, 
    saldo.ano   = YEAR(OLD.fecha), 
    saldo.mes   = MONTH(OLD.fecha), 
    saldo.debe  = OLD.debe*(-1), 
    saldo.haber = OLD.haber*(-1) 
    ON DUPLICATE KEY UPDATE  
        saldo.debe  = saldo.debe  + (OLD.debe*(-1)), 
        saldo.haber = saldo.haber + (OLD.haber*(-1));
		
    INSERT INTO saldo SET 
      saldo.cuenta = NEW.cuenta, 
      saldo.ano    = YEAR(NEW.fecha), 
      saldo.mes    = MONTH(NEW.fecha), 
      saldo.debe   = NEW.debe, 
      saldo.haber  = NEW.haber
      ON DUPLICATE KEY UPDATE  
         saldo.debe  = saldo.debe  + NEW.debe, 
         saldo.haber = saldo.haber + NEW.haber ;
END;//
DELIMITER ;

TRIGGER delete

DELIMITER //
CREATE TRIGGER apuntesD  AFTER DELETE ON apuntes
FOR EACH ROW 
BEGIN 
    INSERT INTO saldo SET 
        saldo.cuenta = OLD.cuenta, 
        saldo.ano   = YEAR(OLD.fecha), 
        saldo.mes   = MONTH(OLD.fecha), 
        saldo.debe  = OLD.debe*(-1), 
        saldo.haber = OLD.haber*(-1) 
        ON DUPLICATE KEY UPDATE  
            saldo.debe  = saldo.debe  + (OLD.debe*(-1)), 
            saldo.haber = saldo.haber + (OLD.haber*(-1)) ;
END;//
DELIMITER ;

Aplicación

SET AUTOCOMMIT = 0;
START TRANSACTION ;
  INSERT INTO apuntes VALUES (1,1,'2014-02-07','Factura 112 PEPE PALO','4300000001',1160,0);
  INSERT INTO apuntes VALUES (1,2,'2014-02-07','Factura 112 PEPE PALO','4770000001',0,160);
  INSERT INTO apuntes VALUES (1,3,'2014-02-07','Factura 112 PEPE PALO','7000000000',0,1000);
  INSERT INTO apuntes VALUES (2,1,'2014-03-20','Cobro Factura 112 PEPE PALO','5700000000',1160,0);
  INSERT INTO apuntes VALUES (2,2,'2014-03-20','Cobro Factura 112 PEPE PALO','4300000001',0,1160);
COMMIT;