Tema 4.2 - Triggers (Disparadores)

Un trigger o disparador es un bloque de código asociado a una tabla o vista que se ejecuta automáticamente, es decir, de manera implícita cuando ocurre algún evento. Los eventos que hace que se dispare un trigger son los siguientes:

1. Una declaración de manipulación de datos (DML) como INSERT, DELETE o UPDATE.
2. Una declaración de definición de base de datos (DDL) como CREATE, ALTER o DROP.
3. Una operación de base de datos como SERVERERROR, LOGON, LOGOFF, STARTUP o SHUTDWON.

Los triggers se crean para conservar y establecer reglas de integridad de los datos, tener coherencia entre los datos entre distintas tablas, registrar los cambios que se efectúen a las tablas e identificación del usuario quien los realizó, etc.

Las diferencias que tienen los triggers con respecto a los procedimientos almacenados y funciones son:

1. No pueden ser invocados directamente, solamente son invocados cuando se intenta modificar una tabla o vista.
2. No reciben ni retornan parámetros.
3. Son apropiados para mantener la integridad de los datos, no para obtener resultados de consultas.

Un trigger tiene asociado:

1. Un evento, que hace referencia a la operación que se efectúa sobre la tabla (INSERT, DELETE o UPDATE). Se debe poner como mínimo una operación de las descritas anteriormente, en caso de que sean más se separan con la instrucción OR.
2. Un momento, que define cuando se dispara el trigger en relación con el evento. Sus posibles valores son: BEFORE, significa que el disparador se activa antes de que se ejecute la operación DML (INSERT, DELETE o UPDATE) sobre la tabla; AFTER, significa que el trigger se activará después de que se ejecute la operación que modificó la tabla e INSTEAD OF que sólo se puede definir sobre las vistas.
3. Un tipo, que indica el número de veces que el cuerpo del trigger se ejecuta. Esto se puede dar de dos maneras: Por cada fila procesada (trigger de fila), es decir, se activa una vez por cada registro afectado por la operación sobre la tabla y se utiliza la sentencia FOR EACH ROW, o los triggers de operación que se activan una sola vez (antes o después de la operación sobre la tabla). Si en la sintaxis no se especifica el tipo, significa que es un trigger de operación.

A continuación, se presenta la sintaxis general que tienen los triggers.

Donde:

1. Los corchetes indican las partes del código que son opcionales.
2. La cláusula REPLACE sobreescribe un trigger existente.
3. nombre_trigger es el nombre que se le va a poner a nuestro disparador.
4. Si el evento es de tipo UPDATE, la cláusula OF nos permite especificar la(s) columna(s) que se disparara. Si no se especifican las columnas después de OF, el trigger se dispara con cualquier columna de la tabla que sea actualizada.
5. nombre_tabla indica la tabla o vista asociada al disparador.
6. La cláusula WHEN sólo es válida para los triggers de fila. Si está presente, el trigger se ejecutará en las filas que cumplan la condición especificada en esta cláusula.

Utilización de :OLD y :NEW en los disparadores

Las pseudocolumnas :OLD y :NEW sólo se utilizan en los triggers de fila para poder acceder al valor de una columna antes de la actualización con :OLD.nom_columna y al valor después de la actualización con :NEW.nom_columna.

Orden de ejecución de los triggers

La ejecución de un trigger es transaccional, es decir, si un trigger de fila afecta a varios registros y una de ella falla, entonces todo el proceso se aborta.

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

El orden de ejecución de los trigger es el siguiente:

1. Los triggers BEFORE de operación, si los hay.
2. Se ejecutan sucesivamente para cada fila:
1. Los disparadores de tipo BEFORE de fila, si los hay.
2. Se ejecuta la operación DML correspondiente.
3. Se ejecutan si existen, los disparadores de tipo AFTER de fila.
3. Por último, se ejecutan los triggers de tipo AFTER de operación.

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:

1. Inserting: Devuelve TRUE cuando el trigger ha sido disparado por un INSERT.
2. Deleting: Devuelve TRUE cuando el trigger ha sido disparado por un DELETE.
3. Updating: Devuelve TRUE cuando el trigger ha sido disparado por un UPDATE.
4. Updating (columna): Devuelve TRUE cuando el trigger ha sido disparado por un UPDATE y la columna ha sido modificada.

Tabla mutante

Al momento en que una tabla está siendo modificada por un trigger, dicha tabla se encuentra en “proceso de mutación”, es decir, que se están haciendo cambios sobre ella y por lo tanto dentro de un trigger no se debe hacer ningún tipo de acceso a esta tabla por medio de operaciones DML (INSERT, DELETE, UPDATE o SELECT).

Para solucionar el error de tabla mutante se pueden seguir los siguientes pasos:

1. Evitar tratamiento a nivel de fila. En vez de tratar a nivel de registro (trigger de fila), se pudiera tratar a nivel de sentencia (trigger de operación) y así evitar poner FOR EACH ROW.
2. Usar Compound Trigger, función que se comenzó a implementar a partir de la versión 11g de Oracle.
3. Usar Transacciones Autónomas. Nuestro trigger se ejecuta en una transacción diferente, y por lo tanto vemos la tabla “consistente”. No se recomienda usar este sistema. Además, las transacciones autónomas se utilizan cuando los datos escritos de una transacción requieren que sean persistentes a pesar de que la transacción se deshaga con ROLLBACK. Para definir una transacción autónoma se emplea las palabras reservadas PRAGMA AUTONOMUS_TRANSACTION y puede aparecer en la sección de declaración de variables de los bloques PL/SQL anónimos, funciones, procedimientos y triggers. Al escribir las palabras reservadas mencionadas anteriormente marcamos el subprograma para que se comporte como una transacción distinta a la del proceso principal, llevando el control de los COMMIT y ROLLBACK. A continuación, se muestra una imagen donde se explica mejor su funcionamiento.

Eliminar trigger

Para eliminar un trigger se utiliza la cláusula DROP.

Ahora mostraremos ejemplos sobre este tema para que se entienda mejor.

Nota: En los ejemplos que se verán a continuación se recomienda que una vez creado y utilizado el trigger en el ejemplo, se elimine el trigger porque puede pasar el caso en que una tabla tenga asociado varios disparadores del mismo evento y esto ocasione que se ejecuten todos a la vez y no podamos observar el resultado esperado.

Ejemplo 1:

La tabla PACIENTE será utilizada para este ejemplo.

Supongamos que el hospital quiere eliminar a aquellos pacientes que tienen una edad mayor o igual a 25 años, pero antes que se eliminen de la base de datos, quieren mostrar en pantalla los nombres de estos pacientes.

Para esto creamos un trigger llamado borrar_paciente la cual se muestra a continuación.

Lo ejecutamos para que este trigger quede guardado en nuestra base de datos.

Luego en otro script ponemos el código PL/SQL para poder borrar los pacientes que tienen la edad mayor a 24 años.

Al ejecutar el script anterior nos imprime los nombres de los pacientes que tienen una edad superior a 24 años.

Ahora nos fijamos en la tabla PACIENTE para verificar si los pacientes mostrados en la imagen anterior, realmente fueron borrados.

Ya que pudimos verificar que el trigger borrar_empleado funciona correctamente, procedemos a ejecutar la sentencia ROLLBACK en un nuevo script para deshacer los cambios que se efectuaron en la base de datos y poder recuperar todos los registros que tenía la tabla PACIENTE antes de hacer la operación DELETE.

Ejemplo 2:

En el ejemplo 1 se evidencia el uso del trigger de fila, ahora es el turno de mostrar cómo se puede implementar el uso de un trigger de tipo operación.

Para esto, usaremos la tabla EMPLEADO.

Suponga que la empresa va a contratar varios empleados y cada vez que se ingrese un empleado se imprimirá en pantalla el salario total de la nómina para que la empresa tenga en mente este dato y tener un presupuesto lo suficientemente alto para cubrir estos costos fijos.

Entonces en un script crearemos nuestro trigger que se llamará sueldo_nomina para que nos imprima dicho valor y lo ejecutamos para que el disparador quede guardado en nuestra base de datos.

Luego en otro script insertemos nuevos empleados a la base de datos usando el bloque PL/SQL.

Al ejecutar el script observaremos como nos imprime el salario de la nómina cada vez que se ingrese un nuevo empleado.

Verificamos si en nuestra tabla EMPLEADO ingresaron estos trabajadores.

Por último, en otro script ejecutamos la sentencia ROLLBACK para deshacer los cambios que hicimos en la base de datos y quedar con los datos que estaban al principio de la inserción.

Ejemplo 3:

Este ejemplo es para ver el uso de los predicados precondicionales. Para esto seguiremos usando la tabla EMPLEADO.

Ahora creamos nuestro trigger que se llamará predicado_precondicional y lo ejecutamos inmediatamente.

Después en otro script escribimos el siguiente código en PL/SQL.

Al ejecutar el script nos imprime el siguiente mensaje en pantalla:

Nos imprime el mensaje dos veces debido a que el trigger utiliza la cláusula FOR EACH ROW y recordemos que esta cláusula activa el disparador una vez por cada registro que se ve afectado por la operación, que en nuestro caso es DELETE y como los empleados que estaban en el departamento 1 eran 2, se activa dos veces el trigger.

Hacemos un ROLLBACK para deshacer los cambios que se hizo en la base de datos y recuperar los dos empleados que fueron eliminados por el DELETE.

Ejemplo 4:

Ahora vamos a ver como ocurre una tabla mutante y como arreglar este error. Utilizaremos la tabla EMPLEADO.

Supongamos que la empresa hizo una revisión para saber cuál departamento está trabajando eficazmente y dando mejores resultados. La revisión arrojó que el departamento 1 fue el mejor, por ende, la empresa decide recompensarlos con un aumento de salario de 400000 pero también quiere saber cuántos empleados fueron beneficiados por este incremento.

Para esto crearemos un trigger llamado tabla_mutante y lo ejecutamos inmediatamente para que quede guardado en nuestra base de datos.

Después escribimos el siguiente código PL/SQL.

Corramos el script y obtendremos el siguiente resultado:

Como se pudo notar, nos mostró un error de tipo tabla mutante; para solucionar esto, simplemente debemos comentar o eliminar el FOR EACH ROW que tenemos en nuestro trigger.

Ahora volvemos a correr la misma actualización y esta vez sí nos dará los resultados esperados.

Y para estar más seguros miramos nuestra tabla EMPLEADO para verificar si se actualizaron o no los salarios de los empleados que pertenecen al departamento 1.

Por último, ejecutamos un ROLLBACK para deshacer los cambios.

Ejemplo 5:

En este ejemplo se observará como aplicar una transacción autónoma para que los datos persistan aun cuando se deshagan los cambios con ROLLBACK.

Para esto crearemos dos tablas nuevas que se llamarán PRUEBA01 y PRUEBA02. La PRUEBA01 tendrá la siguiente estructura y datos.

Mientras que la PRUEBA02 tendrá la misma estructura que PRUEBA01, con la diferencia de que éste no tendrá ningún registro.

Luego implementamos dos procedimientos almacenados insertar_prueba01 e insertar_prueba02 que su función será ingresar filas a cada una de estas tablas (PRUEBA01 y PRUEBA02), con la particularidad que además de que insertar_prueba01 llamará al procedimiento insertar_prueba02, este último procedimiento será marcado como una transacción autónoma, es decir, una transacción totalmente independiente a la principal.

Ahora en un nuevo script escribimos el siguiente código y lo ejecutamos:

Si nos fijamos en las tablas PRUEBA01 y PRUEBA02, podemos darnos cuenta que la sentencia ROLLBACK afectó solamente a los registros de la tabla PRUEBA01 debido a que el procedimiento asociado a la tabla PRUEBA02 ejecuta una transacción independiente a la principal y en dicha transacción se implementa la sentencia COMMIT para confirmar los cambios en la BD.