PL/SQL – Triggers

En este capítulo, hablaremos de los Triggers en PL/SQL. Los Triggers son programas almacenados, que se ejecutan o disparan automáticamente cuando ocurren algunos eventos. Los Triggers son, de hecho, escritos para ser ejecutados en respuesta a cualquiera de los siguientes eventos –

• Una sentencia de manipulación de base de datos (DML) (DELETE, INSERT, o UPDATE)

• Una sentencia de definición de base de datos (DDL) (CREATE, ALTER, o DROP).

• Una operación de base de datos (SERVERERROR, LOGON, LOGOFF, STARTUP o SHUTDOWN).

Los activadores pueden definirse en la tabla, vista, esquema o base de datos con la que se asocia el evento.

Beneficios de los triggers

Los triggers pueden escribirse con los siguientes fines –

• Generar algunos valores de columnas derivadas de forma automática
• Reforzar la integridad referencial
• Registrar eventos y almacenar información sobre el acceso a la tabla
• .
• Auditar
• Replicación sincrónica de tablas
• Imponer autorizaciones de seguridad
• Impedir transacciones no válidas

Crear Triggers

La sintaxis para crear un trigger es –

CREATE TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF } {INSERT | UPDATE | DELETE} ON table_name WHEN (condition) DECLARE Declaration-statements BEGIN Executable-statements EXCEPTION Exception-handling-statements END; 

Donde,

• CREAR DISPARO nombre_disparador – Crea o reemplaza un disparador existente con el nombre_disparador.

• {Antes | Después | En lugar de} – Especifica cuándo se ejecutará el disparador. La cláusula INSTEAD OF se utiliza para crear un trigger en una vista.

• {INSERT | UPDATE | DELETE} – Especifica la operación DML.

• Especifica el nombre de la columna que se actualizará.

• Especifica el nombre de la tabla asociada al trigger.

• – Esto permite referir valores nuevos y antiguos para varias sentencias DML, como INSERT, UPDATE y DELETE.

• – Esto especifica un trigger a nivel de fila, es decir, el disparador se ejecutará para cada fila que se vea afectada. De lo contrario, el desencadenante se ejecutará sólo una vez cuando se ejecute la sentencia SQL, lo que se denomina desencadenante a nivel de tabla.

• WHEN (condición) – Esto proporciona una condición para las filas para las que se dispararía el desencadenante. Esta cláusula sólo es válida para los disparadores de nivel de fila.

Ejemplo

Para empezar, utilizaremos la tabla CLIENTES que habíamos creado y utilizado en los capítulos anteriores –

Select * from customers; +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | +----+----------+-----+-----------+----------+ 

El siguiente programa crea un trigger a nivel de fila para la tabla clientes que se dispararía para las operaciones INSERT o UPDATE o DELETE realizadas en la tabla CLIENTES. Este trigger mostrará la diferencia salarial entre los valores antiguos y los nuevos –

CREATE OR REPLACE TRIGGER display_salary_changes BEFORE DELETE OR INSERT OR UPDATE ON customers FOR EACH ROW WHEN (NEW.ID > 0) DECLARE sal_diff number; BEGIN sal_diff := :NEW.salary - :OLD.salary; dbms_output.put_line('Old salary: ' || :OLD.salary); dbms_output.put_line('New salary: ' || :NEW.salary); dbms_output.put_line('Salary difference: ' || sal_diff); END; / 

Cuando el código anterior se ejecuta en el prompt de SQL, produce el siguiente resultado –

Trigger created.

Aquí hay que tener en cuenta los siguientes puntos –

• Las referenciasOLD y NEW no están disponibles para los triggers a nivel de tabla, más bien se pueden utilizar para los triggers a nivel de registro.

• Si quiere consultar la tabla en el mismo trigger, entonces debe utilizar la palabra clave AFTER, porque los triggers pueden consultar la tabla o modificarla de nuevo sólo después de que se apliquen los cambios iniciales y la tabla vuelva a estar en un estado consistente.

• El trigger anterior se ha escrito de tal manera que se disparará antes de cualquier operación DELETE o INSERT o UPDATE en la tabla, pero puede escribir su trigger en una sola o múltiples operaciones, por ejemplo BEFORE DELETE, que se disparará siempre que un registro se elimine utilizando la operación DELETE en la tabla.

Desencadenando un Trigger

Realicemos algunas operaciones DML en la tabla CLIENTES. Aquí hay una sentencia INSERT, que creará un nuevo registro en la tabla –

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (7, 'Kriti', 22, 'HP', 7500.00 ); 

Cuando se crea un registro en la tabla CLIENTES, el trigger create anterior, display_salary_changes se disparará y mostrará el siguiente resultado –

Old salary: New salary: 7500 Salary difference:

Debido a que se trata de un nuevo registro, el salario antiguo no está disponible y el resultado anterior viene como nulo. Realicemos ahora una operación DML más en la tabla CLIENTES. La sentencia UPDATE actualizará un registro existente en la tabla –

UPDATE customers SET salary = salary + 500 WHERE id = 2; 

Cuando se actualiza un registro en la tabla CUSTOMERS, el trigger create anterior, display_salary_changes se disparará y mostrará el siguiente resultado –

Old salary: 1500 New salary: 2000 Salary difference: 500