consecuencia de una determinada instrucción SQL (INSERT, UPDATE o DELETE) sobre dicha tabla.
7.3.1. Declaración de los triggers.
La sintaxis para crear estos triggers es la siguiente:
CREATE [OR REPLACE] TRIGGER nombre_trigger {BEFORE|AFTER|INSTEAD OF}
{DELETE|INSERT|UPDATE [OF col1, col2, ..., colN] [OR {DELETE|INSERT|UPDATE [OF col1, col2, ..., colN]...]} ON nombre_tabla
[FOR EACH ROW [WHEN (<condición>)]] DECLARE
-- variables locales BEGIN
-- Sentencias [EXCEPTION]
-- Sentencias de control de excepción END nombre_trigger;
El uso de OR REPLACE permite sobrescribir un trigger existente. Si se omite, y el trigger existe, se producirá un error al ejecutar el comando CREATE TRIGGER.
Los triggers pueden definirse para las operaciones INSERT, UPDATE o DELETE, y pueden ejecutarse antes o después de la operación. Los modificadores BEFORE y AFTER indican que el trigger se lanzará antes o después de ejecutarse la sentencia SQL. 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 estos triggers puede ser a nivel de fila o de comando. El modificador FOR EACH ROW indica que el trigger se disparará cada vez que se realizan operaciones sobre una fila de la tabla. Si se acompaña del modificador WHEN, se puede establece una restricción; el trigger solo actuará sobre las filas que satisfagan la restricción. (La cláusula WHEN sólo es válida para los triggers con nivel de fila.)
7.3.2. 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 triggers se activan al ejecutarse la sentencia SQL:
• Si existe, se ejecuta el disparador de tipo BEFORE (disparador previo) con nivel de comando. • Para cada fila a la que afecte el comando:
- Se ejecuta si existe, el disparador de tipo BEFORE con nivel de fila. - Se ejecuta el propio comando.
- Se ejecuta si existe, el disparador de tipo AFTER (disparador posterior) con nivel de fila. • Se ejecuta, si existe, el disparador de tipo AFTER con nivel de comando.
7.3.3. Restricciones de los triggers.
El cuerpo de un trigger es un bloque PL/SQL. Cualquier comando que sea legal en un bloque PL/SQL, es legal en el cuerpo de un trigger, con las siguientes restricciones:
- Un trigger no puede emitir ninguna orden de control de transacciones: COMMIT, ROLLBACK o
SAVEPOINT. El trigger se activa como parte de la ejecución del comando que provocó el disparo, y forma parte de la misma transacción que dicho comando. Cuando el comando que provoca el disparo es confirmado o cancelado, se confirma o cancela también el trabajo realizado por el trigger.
- Por razones idénticas, ningún procedimiento o función llamado por el trigger puede emitir órdenes de control de transacciones.
- El cuerpo del trigger no puede contener ninguna declaración de variables LONG o LONG RAW.
7.3.4. Utilización de las variables globales «OLD» y «NEW».
Dentro del ámbito de un trigger disponemos de las variables globales OLD y NEW. Estas variables se utilizan del mismo modo que cualquier otra variable PL/SQL, con la salvedad de que no es necesario declararlas; son del tipo %ROWTYPE de la tabla asociada al trigger y contienen una copia del registro antes (OLD) y después (NEW) de la acción SQL que ha disparado el trigger. Utilizando estas variables podemos acceder a los datos que se están insertando, actualizando o borrando.
La siguiente tabla muestra los valores de OLD y NEW según el comando que dispara el trigger. Acción SQL OLD NEW
valor NULL. orden.
UPDATE Valores originales de la fila, antes de la
actualización. Nuevos valores que serán escritos cuando se complete la orden.
DELETE Valores, antes del borrado de la fila. No definidos; todos los campos toman el valor NULL.
Nota. Los registros OLD y NEW son sólo válidos dentro de los triggers con nivel de fila (con la especificación FOR EACH ROW).
Por ejemplo, si queremos lanzar un trigger antes de que se actualice una nota, pero sólo si el nuevo valor es mayor que el antiguo valor, podríamos utilizar el siguiente código:
CREATE OR REPLACE TRIGGER TR_Nota_01 BEFORE UPDATE ON Nota
FOR EACH ROW WHEN (NEW.valor > OLD.valor) BEGIN
-- Código del Trigger END ;
El siguiente ejemplo muestra un trigger que inserta automáticamente un registro de matrícula con el año actual cada vez que insertamos un nuevo alumno en la base de datos (en este ejemplo se presupone creado un objeto secuencia denominado SQ_IDMatricula):
CREATE OR REPLACE TRIGGER TR_Matricula_01 AFTER INSERT ON Alumno
FOR EACH ROW BEGIN
INSERT INTO Matricula (idMatricula, nif, año)
VALUES (SQ_IDMatricula.NEXT, :NEW.nif, EXTRACT(YEAR FROM SYSDATE)); END ;
El trigger se ejecutará automáticamente cuando sobre la tabla Alumno se ejecute una sentencia INSERT como:
INSERT INTO Alumno (nif, nombre, apellidos, localidad) VALUES ('66666666H', 'Juan' , 'Salgado Rey', 'Madrid');
Nota. Cuando se usa NEW y OLD en el cuerpo del trigger deben ir precedidos de dos punto (:); sin embargo, cuando se usan en la cláusula WHEN de FOR EACH ROW no deben ir precedidos de dos puntos.
7.3.5. Utilización de las funciones «INSERTING», «UPDATING» y «DELETING».
Dentro de un trigger en el que se disparan distintos tipos de órdenes DML, hay tres funciones booleanas que pueden emplearse para determinar de qué operación se trata. Estas funciones son INSERTING, UPDATING y
DELETING.
Su comportamiento es el siguiente: Función Retorna
INSERTING TRUE si el comando de disparo es INSERT; FALSE en otro caso.
UPDATING TRUE si el comando de disparo es UPDATE; FALSE en otro caso.
DELETING TRUE si el comando de disparo es DELETE; FALSE en otro caso.
Como ejemplo, el siguiente trigger normaliza el precio de un libro cuando se realizan inserciones o actualizaciones en la tabla LIBRO. Si se inserta un nuevo registro redondea el precio, y se actualiza el precio conserva el antiguo valor si es mayor que el nuevo.
CREATE OR REPLACE TRIGGER TR_Normaliza_Titulo BEFORE INSERT OR UPDATE OF precio ON Libro FOR EACH ROW
DECLARE BEGIN
IF INSERTING THEN -- se está realizando una inserción :NEW.precio := ROUND(:NEW.precio, 2);
END IF;
IF UPDATING AND :NEW.precio<ODL.precio THEN -- se está realizando una actualización :NEW.precio := :OLD.precio;
END IF; END;
7.3.6. Uso de triggers para asignar claves automáticamente.
Oracle no permite crear claves autonuméricas, pero mediante el uso de secuenciadores y triggers podemos simular esta funcionalidad.
Por ejemplo, si hemos creado la tabla Matricula con un campo clave idMatricula de tipo INTEGER, podemos omitir la asignación de este campo cada vez que hagamos una inserción de producto de la siguiente manera:
1) Creamos un secuenciador con el comando:
CREATE SEQUENCE SQ_IDMatricula;
2) Creamos un trigger como el siguiente:
CREATE TRIGGER TR_Pon_IDMatricula BEFORE INSERT
ON Matricula FOR EACH ROW BEGIN
SELECT SQ_IDMatricula.NEXTVAL INTO :NEW.idMatricula FROM DUAL; END;
Ahora, cada vez que se ejecute una instrucción INSERT sobre la tabla Matricula, tanto si pasamos un valor para
idMatricula como si no, se lanzará un trigger que asigne automáticamente el valor.