Un disparador de base de datos es un código de procedimiento que se ejecuta automáticamente en respuesta a ciertos eventos en una tabla o vista particular de una base de datos . El disparador se utiliza principalmente para mantener la integridad de la información en la base de datos. Por ejemplo, cuando se agrega un nuevo registro (que representa un nuevo trabajador) a la tabla de empleados, también se deben crear nuevos registros en las tablas de impuestos, vacaciones y salarios. Los disparadores también se pueden utilizar para registrar datos históricos, por ejemplo, para realizar un seguimiento de los salarios anteriores de los empleados.
A continuación se muestra una serie de descripciones de cómo se activan algunos activadores de soporte de DBMS populares .
Además de los activadores que se activan (y ejecutan código PL/SQL ) cuando se modifican los datos, Oracle 10g admite activadores que se activan cuando se modifican objetos de nivel de esquema (es decir, tablas) y cuando ocurren eventos de inicio o cierre de sesión del usuario.
Los cuatro tipos principales de desencadenantes son:
A partir de Oracle 8i , los eventos de base de datos (inicios de sesión, cierres de sesión, inicios) pueden activar activadores de Oracle. [1]
Una lista de todos los eventos de activación disponibles en Microsoft SQL Server para desencadenadores DDL está disponible en Microsoft Docs . [2]
La ejecución de acciones condicionales en los activadores (o la prueba de datos después de una modificación) se realiza mediante el acceso a las tablas temporales Insertadas y Eliminadas .
Se introdujo soporte para activadores en 1997. La siguiente funcionalidad en SQL:2003 no se había implementado anteriormente en PostgreSQL:
Sinopsis:
CREAR DISPARADOR nombre { ANTES | DESPUÉS } { evento [ O ... ] } EN LA TABLA [ PARA [ CADA ] { FILA | DECLARACIÓN } ] EJECUTAR PROCEDIMIENTO funcname ( argumentos ) Firebird admite múltiples activadores de nivel de fila, ANTES o DESPUÉS, INSERTAR, ACTUALIZAR, ELIMINAR (o cualquier combinación de ellos) por tabla, donde siempre son "además de" los cambios de tabla predeterminados, y el orden de los activadores en relación con los demás se puede especificar donde de otra manera sería ambiguo (cláusula POSITION). Los activadores también pueden existir en las vistas, donde siempre son "en lugar de" activadores, reemplazando la lógica de vista actualizable predeterminada. (Antes de la versión 2.1, los activadores en las vistas consideradas actualizables se ejecutaban además de la lógica predeterminada).
Firebird no genera excepciones de tabla mutante (como Oracle) y los activadores se anidarán y recursarán de forma predeterminada según sea necesario (SQL Server permite la anidación pero no la recursión, de forma predeterminada). Los activadores de Firebird usan variables de contexto NUEVAS y VIEJAS (no tablas Insertadas y Eliminadas) y proporcionan indicadores de ACTUALIZACIÓN, INSERCIÓN y ELIMINACIÓN para indicar el uso actual del activador.
{ CREAR | RECREAR | CREAR O MODIFICAR } TRIGGER nombre PARA { nombre de tabla | nombre de vista } [ ACTIVO | INACTIVO ] { ANTES | DESPUÉS } { INSERTAR [ O ACTUALIZAR ] [ O ELIMINAR ] | ACTUALIZAR [ O INSERTAR ] [ O ELIMINAR ] | ELIMINAR [ O ACTUALIZAR ] [ O INSERTAR ] } [ POSICIÓN n ] COMO COMIENZA .... FIN A partir de la versión 2.1, Firebird también admite los siguientes activadores a nivel de base de datos:
Los activadores a nivel de base de datos pueden ayudar a aplicar restricciones de varias tablas o emular vistas materializadas . Si se genera una excepción en un activador TRANSACTION COMMIT, los cambios realizados por el activador hasta el momento se revierten y se notifica a la aplicación cliente, pero la transacción permanece activa como si nunca se hubiera solicitado COMMIT; la aplicación cliente puede continuar realizando cambios y volver a solicitar COMMIT.
Sintaxis para activadores de bases de datos:
{ CREAR | RECREAR | CREAR O MODIFICAR } nombre del DISPARADOR [ ACTIVO | INACTIVO ] EN { CONECTAR | DESCONECTAR | INICIO DE TRANSACCIÓN | CONFIRMACIÓN DE TRANSACCIÓN | REVERSIÓN DE TRANSACCIÓN } [ POSICIÓN n ] COMO INICIO ..... FIN En la versión 5.0 de MySQL, lanzada en 2005, se agregó soporte limitado para activadores en el DBMS MySQL/MariaDB. [4]
A partir de la versión 8.0, permiten el uso de activadores DDL (lenguaje de definición de datos) y DML (lenguaje de manipulación de datos). También permiten utilizar cualquiera de los dos tipos de activadores DDL (AFTER o BEFORE) para definir activadores. Se crean mediante la cláusula CREATE TRIGGER y se eliminan mediante la cláusula DROP TRIGGER . La sentencia que se invoca cuando ocurre un evento se define después de la cláusula FOR EACH ROW , seguida de una palabra clave ( SET o BEGIN ), que indica si lo que sigue es una expresión o una sentencia respectivamente. [5]
IBM DB2 para sistemas distribuidos, conocido como DB2 para LUW (LUW significa Linux , UNIX , W indows), admite tres tipos de disparadores: antes del disparador, después del disparador y en lugar del disparador. Se admiten disparadores a nivel de sentencia y a nivel de fila. Si hay más disparadores para la misma operación en la tabla, el orden de activación se determina mediante los datos de creación del disparador. Desde la versión 9.7, IBM DB2 admite transacciones autónomas. [6]
Antes de que el disparador se active, se comprueban los datos y se decide si se debe permitir la operación. Si se lanza una excepción desde antes del disparador, se cancela la operación y no se modifica ningún dato. En DB2, los disparadores anteriores son de solo lectura: no se pueden modificar los datos en los disparadores anteriores. Los disparadores posteriores están diseñados para el posprocesamiento después de que se realizó el cambio solicitado. Los disparadores posteriores pueden escribir datos en tablas y, a diferencia de otras bases de datos [¿ cuáles? ], se pueden escribir en cualquier tabla, incluida la tabla en la que opera el disparador. En lugar de los disparadores, se utilizan para hacer que las vistas sean escribibles.
Los activadores normalmente se programan en lenguaje SQL PL .
CREAR [ TEMP | TEMPORAL ] DISPARADOR [ SI NO EXISTE ] [ nombre_base_datos .] nombre_disparador [ ANTES | DESPUÉS | EN LUGAR DE ] { ELIMINAR | INSERTAR | ACTUALIZAR [ DE nombre_columna [, nombre_columna ]...] } EN { nombre_tabla | nombre_vista } [ PARA CADA FILA ] [ CUANDO la condición es obligatoria ] COMIENZA ... FIN SQLite solo admite activadores a nivel de fila, no a nivel de declaración.
Las vistas actualizables, que no son compatibles con SQLite, se pueden emular con activadores INSTEAD OF.
Un ejemplo de implementación de triggers en una base de datos no relacional puede ser Sedna , que proporciona soporte para triggers basados en XQuery . Los triggers en Sedna fueron diseñados para ser análogos a los triggers de SQL:2003 , pero se basan de forma nativa en lenguajes de consulta y actualización XML ( XPath , XQuery y lenguaje de actualización XML).
En Sedna, se establece un disparador en cualquier nodo de un documento XML almacenado en una base de datos. Cuando se actualizan estos nodos, el disparador ejecuta automáticamente las consultas XQuery y las actualizaciones especificadas en su cuerpo. Por ejemplo, el siguiente disparador cancela la eliminación del nodo de persona si hay subastas abiertas a las que hace referencia esta persona:
CREAR DISPARADOR "trigger3" ANTES DE ELIMINAR EN doc( "subasta" )/ sitio // persona PARA CADA NODO HACER { si ( existe ( $ DONDE // subasta_abierta / postor / personref / @person = $ OLD / @id )) entonces ( ) de lo contrario $ OLD ; } Para comprender cómo funciona el comportamiento de los activadores, debe conocer los dos tipos principales de activadores: los activadores de nivel de fila y los de nivel de instrucción. La distinción entre ambos es cuántas veces se ejecuta el código dentro del activador y en qué momento.
Supongamos que tiene un disparador que está diseñado para ser llamado en una ACTUALIZACIÓN de una determinada tabla. Los disparadores a nivel de fila se ejecutarían una vez por cada fila afectada por la ACTUALIZACIÓN. Es importante tener en cuenta que si ninguna fila se ve afectada por el comando ACTUALIZACIÓN, el disparador no ejecutará ningún código dentro del disparador. Los disparadores a nivel de instrucción se llamarán una vez independientemente de cuántas filas se vean afectadas por la ACTUALIZACIÓN. Aquí es importante tener en cuenta que incluso si el comando ACTUALIZACIÓN no afectó a ninguna fila, el código dentro del disparador se ejecutará una vez.
El uso de las opciones BEFORE y AFTER [7] determina cuándo se llama al disparador. Suponga que tiene un disparador que se llama en un INSERT en una tabla determinada. Si su disparador usa la opción BEFORE, el código dentro del disparador se ejecutará antes de que ocurra el INSERT en la tabla. Un uso común del disparador BEFORE es verificar los valores de entrada del INSERT o modificar los valores en consecuencia. Ahora digamos que tenemos un disparador que usa AFTER en su lugar. El código dentro del disparador se ejecuta después de que se produce el INSERT en la tabla. Un ejemplo de uso de este disparador es crear un historial de auditoría de quién ha realizado inserciones en la base de datos, manteniendo un registro de los cambios realizados. Al usar estas opciones, debe tener en cuenta algunas cosas. La opción BEFORE no le permite modificar las tablas, por eso la validación de entrada es un uso práctico. El uso de disparadores AFTER le permite modificar tablas, como insertar en una tabla de historial de auditoría.
Al crear un disparador para determinar si es de nivel de instrucción o de fila, simplemente incluya la cláusula FOR EACH ROW para un nivel de fila u omita la cláusula para un nivel de instrucción. Tenga cuidado al usar comandos INSERT / UPDATE / DELETE adicionales dentro de su disparador, porque es posible la recursión del disparador , lo que causa un comportamiento no deseado. En los ejemplos a continuación, cada disparador modifica una tabla diferente. Al observar lo que se está modificando, puede ver algunas aplicaciones comunes de cuándo se usan diferentes tipos de disparadores.
El siguiente es un ejemplo de sintaxis de Oracle de un disparador de nivel de fila que se llama DESPUÉS de una actualización PARA CADA FILA afectada. Este disparador se llama en una actualización de una base de datos de la guía telefónica. Cuando se llama al disparador, agrega una entrada en una tabla separada llamada phone_book_audit. También tenga en cuenta que los disparadores pueden aprovechar objetos de esquema como secuencias, [8] en este ejemplo, audit_id_sequence.nexVal se utiliza para generar claves primarias únicas en la tabla phone_book_audit.
CREAR O REEMPLAZAR EL DISPARADOR phone_book_audit DESPUÉS DE ACTUALIZAR EN phone_book_audit PARA CADA FILA COMENZAR INSERTAR EN phone_book_audit ( audit_id , audit_change , audit_l_name , audit_f_name , audit_old_phone_number , audit_new_phone_number , audit_date ) VALORES ( audit_id_sequence.nextVal , ' Actualizar ' , : OLD.apellido , : OLD.nombre , : OLD.número_teléfono , : NEW.número_teléfono , SYSDATE ) ; FIN ; Ahora se llama a una ACTUALIZACIÓN en la tabla phone_book para las personas con el apellido 'Jones'.
ACTUALIZAR libreta telefónica ESTABLECER número_teléfono = '111-111-1111' DONDE apellido = 'Jones' ;
Observe que la tabla phone_number_audit ahora contiene dos entradas. Esto se debe a que la base de datos tiene dos entradas con el apellido "Jones". Dado que la actualización modificó dos valores de fila independientes, el disparador creado se llamó dos veces; una después de cada modificación.
Un disparador de sentencia de sintaxis de Oracle que se llama después de una ACTUALIZACIÓN en la tabla phone_book. Cuando se llama al disparador, realiza una inserción en la tabla phone_book_edit_history
CREAR O REEMPLAZAR EL DISPARADOR phone_book_history DESPUÉS DE ACTUALIZAR EN phone_book COMENZAR INSERTAR EN phone_book_edit_history ( audit_history_id , nombre_usuario , modificación , fecha_edición ) VALORES ( audit_history_id_sequence . nextVal , USUARIO , 'Actualizar' , SYSDATE ); FIN ; Ahora se realiza exactamente la misma actualización que en el ejemplo anterior, pero esta vez con un disparador a nivel de declaración.
ACTUALIZAR libreta telefónica ESTABLECER número_teléfono = '111-111-1111' DONDE apellido = 'Jones' ; El resultado muestra que el disparador solo se llamó una vez, aunque la actualización cambió dos filas.
Este ejemplo demuestra un disparador BEFORE EACH ROW que modifica el INSERT mediante una condición WHEN. Si el apellido tiene más de 10 letras, mediante la función SUBSTR [9] cambiamos el valor de la columna last_name por una abreviatura.
CREAR O REEMPLAZAR EL DISPARADOR phone_book_insert ANTES DE INSERTAR EN phone_book PARA CADA FILA CUANDO ( LONGITUD ( new . last_name ) > 10 ) COMIENZA : new . last_name : = SUBSTR (: new . last_name , 0 , 1 ); FIN ; Ahora se está realizando una INSERCIÓN de alguien con un nombre grande.
INSERTAR EN phone_book VALORES ( 6 , 'ApellidoMuyMuyLargo' , 'Erin' , 'Minneapolis' , 'MN' , '989 University Drive' , '123-222-4456' , 55408 , TO_DATE ( '11/21/1991' , 'MM/DD/AAAA' )); El disparador funcionó según el resultado anterior, modificando el valor de INSERT antes de ejecutarse.
El uso de un disparador de declaración BEFORE es particularmente útil cuando se aplican restricciones de base de datos. [10] Este ejemplo demuestra cómo aplicar una restricción a alguien llamado "SOMEUSER" en la tabla phone_book.
CREAR O REEMPLAZAR EL DISPARADOR hauschbc ANTES DE INSERTAR EN ALGÚNUSUARIO . phone_book BEGIN RAISE_APPLICATION_ERROR ( num => - 20050 , msg => 'El mensaje de error va aquí.' ); END ; Ahora, cuando "SOMEUSER" inicia sesión después de intentar cualquier INSERCIÓN, se mostrará este mensaje de error:
Error de SQL: ORA-20050: El mensaje de error va aquí.
Los errores personalizados como este tienen una restricción sobre cómo se puede definir la variable num. Debido a los numerosos otros errores predefinidos, esta variable debe estar en el rango de −20000 a −20999.
[...] Los activadores a nivel de sistema [...] se introdujeron en Oracle8i. [...] Los activadores a nivel de sistema se activan en eventos específicos del sistema, como inicio de sesión, cierre de sesión, inicio de base de datos, ejecución de DDL y error de servidor [...].