Índice

Introducción

Los disparadores (o triggers) son bloques de código PL/SQL asociados a una tabla y que se ejecutan automáticamente como reacción a una operación DML específica (INSERT, UPDATE o DELETE) sobre dicha tabla.



En definitiva, los disparadores son eventos a nivel de tabla que se ejecutan automáticamente cuando se realizan ciertas operaciones sobre la tabla.

Para crear un disparador utilizaremos la siguiente instrucción:

CREATE {OR REPLACE} TRIGGER nombre_disp
  [BEFORE|AFTER]
  [DELETE|INSERT|UPDATE {OF columnas}] [ OR [DELETE|INSERT|UPDATE {OF columnas}]...]
  ON tabla
  [FOR EACH ROW [WHEN condicion disparo]]
[DECLARE]
  -- Declaración de variables locales
BEGIN
  -- Instrucciones de ejecución
[EXCEPTION]
  -- Instrucciones de excepción
END;

El uso de OR REPLACE permite sobrescribir un trigger existente. Si se omite, y el trigger existe, se producirá, un error.

En principio, dentro del cuerpo de programa del trigger podemos hacer uso de cualquier orden de consulta o manipulación de la BD, y llamadas a funciones o procedimientos siempre que:

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:

CREATE TRIGGER audit_trigger BEFORE INSERT OR DELETE OR UPDATE
  ON classified_table FOR EACH ROW
BEGIN
  IF INSERTING THEN
    INSERT INTO audit_table
    VALUES (USER || ' is inserting' ||
    ' new key: ' || :new.key);
  ELSIF DELETING THEN
    INSERT INTO audit_table
    VALUES (USER || ' is deleting' ||
    ' old key: ' || :old.key);
  ELSIF UPDATING('FORMULA') THEN
    INSERT INTO audit_table
    VALUES (USER || ' is updating' ||
    ' old formula: ' || :old.formula ||
    ' new formula: ' || :new.formula);
  ELSIF UPDATING THEN
    INSERT INTO audit_table
    VALUES (USER || ' is updating' ||
    ' old key: ' || :old.key ||
    ' new key: ' || :new.key);
  END IF;
END;

Tipos de triggers

Los triggers pueden definirse para las operaciones INSERT, DELETE o Update, y pueden dispararse antes o después de la operación. Finalmente, el nivel de los disparadores puede ser la fila o registro o la orden.

El modificador BEFORE o AFTER indica que el trigger se ejecutará antes o después de ejecutarse la sentencia SQL definida por DELETE, INSERT o UPDATE. Si incluimos el modificador OF el trigger solo se ejecutará cuando la sentencia SQL afecte a los campos incluidos en la lista.

El alcance de los disparadores puede ser la fila o de orden. El modificador FOR EACH ROW indica que el trigger se disparará cada vez que se realizan operaciones sobre cada fila de la tabla. Si se acompaña del modificador WHEN, se establece una restricción; el trigger solo actuará, sobre las filas que satisfagan la restricción.

Tipos de disparadores.

CategoriaValoresComentarios
OrdenINSERT, DELETE, UPDATEDefine que tipo de operación DML provoca laactivación del trigger
TemporalizaciónBEFORE o AFTERDefine si el disparador se activa antes o después deque se ejecute la operación DML
NivelFila u OrdenLos disparadores con nivel de fila se activan unavez por cada fila afectada por la orden que provocóel disparo. Los Triggers con nivel de orden seactivan sólo una vez, antes o después de la orden.Los disparadores con nivel de fila se identificanpor la cláusula FOR EACH ROW en la definicióndel disparador.

Orden de ejecución de los triggers

Una misma tabla puede tener varios triggers asociados. En tal caso es necesario conocer el orden en el que se van a ejecutar.

Los disparadores se activan al ejecutarse la sentencia SQL.

Restricciones de los Triggers.

El cuerpo de un disparador es un bloque PL/SQL. Cualquier orden que sea legal en un bloque PL/SQL , es legal en el cuerpo de un disparador, con las siguientes restricciones:

Utilización de :old y :new en los disparadores con nivel de fila.

Un disparador con nivel de fila se ejecuta una vez por cada fila procesada por la orden que provoca el disparo. Dentro del disparador, puede accederse a la fila que está siendo actualmente procesada utilizando, para ello, dos pseudo-registros, :old y :new.

En principio tanto :old como :new son del tipo tabla_disparo%ROWTYPE;

Orden de Disparo:old:new
INSERTNo definido; todos los campos tomanel valor NULL.Valores que serán insertados cuandose complete la orden
UPDATEValores originales de la fila, antes dela actualización.Nuevos valores que serán escritoscuando se complete la orden.
DELETEValores originales, antes del borradode la fila.No definido; todos los campos tomanel valor NULL.

Ejemplo:

CREATE TRIGGER scott.emp_permit_changes
  BEFORE DELETE OR INSERT OR UPDATE ON scott.emp
DECLARE
  dummy INTEGER;
BEGIN
  /* If today is a Saturday or Sunday, then return an error.
  IF (TO_CHAR(SYSDATE, 'DY') = 'SAT' OR TO_CHAR(SYSDATE, 'DY') = 'SUN') THEN
    raise_application_error( -20501,'May not change employee table during the weekend');
  END IF;

  /* Compare today's date with the dates of all
  company holidays. If today is a company holiday,
  then return an error.*/

  SELECT COUNT(*) INTO dummy FROM company_holidays
  WHERE day = TRUNC(SYSDATE);
  IF dummy > 0 THEN
    raise_application_error( -20501,'May not change employee table during a holiday');
  END IF;

  /*If the current time is before 8:00AM or after
  6:00PM, then return an error. */

  IF (TO_CHAR(SYSDATE, 'HH24') < 8 OR TO_CHAR(SYSDATE, 'HH24') >= 18) THEN
    raise_application_error( -20502, 'May only change employee table during working hours');
  END IF;
END;

La cláusula WHEN

La cláusula WHEN sólo es válida para disparadores con nivel de fila. Si está presente, el cuerpo del disparador sólo se ejecutará para las filas que cumplan la condición especificada en la cláusula.

CREATE TRIGGER tr1
  BEFORE INSERT OR UPDATE OF salario ON scott.emp
  FOR EACH ROW WHEN (new.job <> 'PRESIDENT')
BEGIN
  /* Cuerpo del disparador */
END;

Esto último es equivalente a:

CREATE TRIGGER tr1
  BEFORE INSERT OR UPDATE OF salario ON scott.emp
  FOR EACH ROW
BEGIN
  IF :new.job <> 'PRESIDENT' THEN
    /* Cuerpo del disparador */
  END IF;
END;

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);

El num_error es un número entero cualquiera, aunque se aconseja que tenga 5 dígitos.

raise_application_error( 20000, No se puede modificar el cliente.);

Tabla Mutando

Cuando se realiza un trigger sobre una tabla, dicha tabla se dice que está en “proceso de mutación”, es decir, que se están realizando cambios sobre ella y que por tanto dentro del trigger no se debe hacer ningún tipo de acceso a dicha tabla con operaciones DML (SELECT, INSERT, DELETE o UPDATE).

Si queremos conocer los valores del registro de la tabla sobre la que se ha disparado el trigger, este último debe estar declarado de forma FOR EACH ROW y acceder a sus valores mediante las pseudocolumnas :NEW y :OLD.

Siguiente tema: PL/SQL - Registros y Tablas