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 en particular en 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 activadores 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 incluye una serie de descripciones de cómo se activan algunos de los activadores de compatibilidad con DBMS más populares .
Además de los activadores que se activan (y ejecutan código PL/SQL ) cuando se modifican datos, Oracle 10g admite activadores que se activan cuando se modifican objetos a nivel de esquema (es decir, tablas) y cuando se producen eventos de inicio o cierre de sesión del usuario.
Los cuatro tipos principales de desencadenantes son:
Desde Oracle 8i , los eventos de la 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 activadores DDL está disponible en Microsoft Docs . [2]
La realización de acciones condicionales en los desencadenadores (o la prueba de datos después de la modificación) se realiza accediendo a las tablas temporales insertadas y eliminadas .
Se introdujo el soporte para desencadenadores en 1997. La siguiente funcionalidad en SQL:2003 no se implementó anteriormente en PostgreSQL:
Sinopsis:
CREAR nombre DEL DISPARADOR { ANTES | DESPUÉS } { evento [ O ... ] } EN LA TABLA [ PARA [ CADA ] { FILA | DECLARACIÓN } ] EJECUTAR PROCEDIMIENTO nombrefunción ( argumentos ) Firebird admite múltiples activadores a nivel de fila, ANTES o DESPUÉS, INSERTAR, ACTUALIZAR, ELIMINAR (o cualquier combinación de los mismos) por tabla, donde siempre son "además de" los cambios predeterminados en la tabla y el orden de los activadores en relación con cada uno. se puede especificar otro donde de otro modo sería ambiguo (cláusula POSITION). También pueden existir activadores en las vistas, donde siempre son activadores "en lugar de", reemplazando la lógica de vista actualizable predeterminada. (Antes de la versión 2.1, los activadores de las vistas consideradas actualizables se ejecutaban además de la lógica predeterminada).
Firebird no genera excepciones de tablas mutantes (como Oracle), y los activadores se anidarán y recurrirán de forma predeterminada según sea necesario (SQL Server permite el anidamiento pero no la recursividad, de forma predeterminada). Los activadores de Firebird utilizan variables de contexto NUEVAS y ANTIGUAS (no tablas insertadas y eliminadas). ,) y proporciona indicadores ACTUALIZACIÓN, INSERCIÓN y ELIMINACIÓN para indicar el uso actual del activador.
{ CREAR | RECREAR | CREAR O ALTERAR } nombre DEL DISPARADOR PARA { nombre de la tabla | ver nombre } [ ACTIVO | INACTIVO ] { ANTES | DESPUÉS } { INSERTAR [ O ACTUALIZAR ] [ O BORRAR ] | ACTUALIZAR [ O INSERTAR ] [ O BORRAR ] | ELIMINAR [ O ACTUALIZAR ] [ O INSERTAR ] } [ POSICIÓN n ] COMO COMIENZO .... FINAL A partir de la versión 2.1, Firebird admite además 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 disparador de TRANSACTION COMMIT, los cambios realizados por el disparador 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 ALTERAR } Nombre del DISPARADOR [ ACTIVO | INACTIVO ] ENCENDIDO { CONECTAR | DESCONECTAR | INICIO DE TRANSACCIÓN | COMPROMISO DE TRANSACCIÓN | REVERSIÓN DE TRANSACCIÓN } [ POSICIÓN n ] COMO COMIENZO ..... FINAL En la versión 5.0 de MySQL, lanzada en 2005, se agregó soporte limitado para activadores en el DBMS MySQL/MariaDB .
A partir de la versión 8.0, permiten activadores DDL (lenguaje de definición de datos) y activadores DML (lenguaje de manipulación de datos). También permiten utilizar cualquier tipo de disparador DDL (DESPUÉS o ANTES) para definir los disparadores. Se crean utilizando la cláusula CREATE TRIGGER y se eliminan utilizando la cláusula DROP TRIGGER . La declaración invocada 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 declaración respectivamente. [5]
IBM DB2 para sistemas distribuidos conocido como DB2 para LUW (LUW significa L inux, U nix, Windows ) admite tres tipos de disparador: Antes del disparador, Después del disparador y En lugar del disparador. Se admiten activadores tanto a nivel de declaración como a nivel de fila. Si hay más activadores para la misma operación en la tabla, el orden de activación se determina mediante los datos de creación del activador. Desde la versión 9.7, IBM DB2 admite transacciones autónomas. [6]
Antes del disparo es para verificar los datos y decidir si se debe permitir la operación. Si se genera una excepción antes del activador, la operación se cancela y no se modifican datos. En DB2, los desencadenadores anteriores son de solo lectura: no se pueden modificar los datos anteriores a los desencadenadores. Los activadores posteriores están diseñados para el procesamiento posterior después de realizar el cambio solicitado. Después de los activadores, se pueden escribir datos en tablas y, a diferencia de algunos [¿ cuáles? ] otras bases de datos que puede escribir en cualquier tabla, incluida la tabla en la que opera el activador. En lugar de activadores, sirven para hacer que las vistas se puedan escribir.
Los disparadores generalmente se programan en lenguaje SQL PL .
CREAR [ TEMP | TEMPORAL ] DISPARADOR [ SI NO EXISTE ] [ nombre_base_dato .] nombre_disparador [ ANTES | DESPUÉS | EN LUGAR DE ] { BORRAR | INSERTAR | ACTUALIZACIÓN [ OF nombre_columna [, nombre_columna ]...] } ON { nombre_tabla | view_name } [ PARA CADA FILA ] [ CUANDO la condición es obligatoria ] COMENZAR ... FINALIZAR SQLite solo admite activadores a nivel de fila, no activadores a nivel de declaración.
Las vistas actualizables, que no son compatibles con SQLite, se pueden emular con activadores EN LUGAR DE.
Un ejemplo de implementación de disparadores en bases de datos no relacionales puede ser Sedna , que proporciona soporte para disparadores basados en XQuery . Los activadores en Sedna fueron diseñados para ser análogos a los activadores SQL:2003 , pero se basan de forma nativa en lenguajes de consulta y actualización XML ( Xpath , XQuery y lenguaje de actualización XML).
Un disparador en Sedna se establece en cualquier nodo de un documento XML almacenado en la base de datos. Cuando estos nodos se actualizan, el activador ejecuta automáticamente las consultas XQuery y las actualizaciones especificadas en su cuerpo. Por ejemplo, el siguiente activador 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 { if ( existe ( $ DONDE // open_auction / postor / personref / @person = $ VIEJO / @id )) entonces ( ) más $ ANTIGUO ; } Para comprender cómo funciona el comportamiento desencadenante, es necesario conocer los dos tipos principales de desencadenantes; estos son activadores de nivel de fila y declaración. La distinción entre los dos es cuántas veces se ejecuta el código dentro del disparador y en qué momento.
Supongamos que tiene un activador que está diseñado para ser invocado en una ACTUALIZACIÓN de una tabla determinada. Los activadores de nivel de fila se ejecutarían una vez por cada fila afectada por la ACTUALIZACIÓN. Es importante tener en cuenta que si el comando ACTUALIZAR no afecta ninguna fila, el disparador no ejecutará ningún código dentro del disparador. Los activadores a nivel de declaració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 ACTUALIZAR no afectó a ninguna fila, el código dentro del activador se ejecutará una vez.
El uso de las opciones ANTES y DESPUÉS [7] determina cuándo se llama al disparador. Supongamos que tiene un disparador que se llama en un INSERT en una tabla determinada. Si su disparador usa la opción ANTES, el código dentro del disparador se ejecutará antes de que ocurra INSERTAR en la tabla. Un uso común del disparador BEFORE es verificar los valores de entrada de INSERT o modificar los valores en consecuencia. Ahora digamos que tenemos un disparador que usa DESPUÉS en su lugar. El código dentro del disparador se ejecuta después de que INSERT ocurre en la tabla. Un ejemplo de uso de este activador es la creación de un historial de auditoría de quién realizó inserciones en la base de datos, realizando un seguimiento de los cambios realizados. Al utilizar estas opciones es necesario tener en cuenta algunas cosas. La opción ANTES no permite modificar tablas, por eso la validación de entradas es un uso práctico. El uso de activadores DESPUÉS le permite modificar tablas, como insertarlas en una tabla de historial de auditoría.
Al crear un activador para determinar si es un nivel de declaración o de fila, simplemente incluya la cláusula FOR CADA FILA para un nivel de fila u omita la cláusula para un nivel de declaración. Tenga cuidado al utilizar comandos INSERT / UPDATE / DELETE adicionales dentro de su activador, ya que es posible la recursión del activador, lo que provoca un comportamiento no deseado. En los ejemplos a continuación, cada disparador modifica una tabla diferente; al observar lo que se modifica, puede ver algunas aplicaciones comunes de cuándo se utilizan diferentes tipos de disparadores.
El siguiente es un ejemplo de sintaxis de Oracle de un activador de nivel de fila que se llama DESPUÉS de una actualización PARA CADA FILA afectada. Este activador se activa cuando se actualiza una base de datos de la guía telefónica. Cuando se llama al activador, agrega una entrada en una tabla separada llamada phone_book_audit. También tenga en cuenta que los activadores 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 PARA CADA FILA COMIENZAR A 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' ,: ANTIGUO . apellido ,: ANTIGUO . nombre_de_nombre , : ANTIGUO . número_teléfono , : NUEVO . número_teléfono , SYSDATE ); FIN ; Ahora llamando a una ACTUALIZACIÓN en la tabla de la guía telefónica para personas con el apellido 'Jones'.
ACTUALIZAR agenda telefónica SET número_teléfono = '111-111-1111' WHERE apellido = 'Jones' ;
Observe que la tabla phone_number_audit ahora está llena con 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 separados, el activador creado se llamó dos veces; una vez después de cada modificación.
Un desencadenador de declaración de sintaxis de Oracle que se llama después de una ACTUALIZACIÓN de la tabla listín telefónico. Cuando se llama al disparador, se inserta en la tabla phone_book_edit_history
CREAR O REEMPLAZAR EL DISPARADOR phone_book_history DESPUÉS DE ACTUALIZAR EN phone_book COMIENZAR A INSERTAR EN phone_book_edit_history ( audit_history_id , nombre de usuario , modificación , editar_fecha ) VALORES ( audit_history_id_sequence . nextVal , USUARIO , 'Actualizar' , SYSDATE ); FIN ; Ahora hacemos exactamente la misma actualización que en el ejemplo anterior, pero esta vez con un activador a nivel de declaración.
ACTUALIZAR agenda telefónica SET número_teléfono = '111-111-1111' WHERE apellido = 'Jones' ; El resultado muestra que el activador solo se llamó una vez, aunque la actualización cambió dos filas.
Este ejemplo demuestra un disparador ANTES DE CADA FILA que modifica INSERT usando un condicional WHEN. Si el apellido tiene más de 10 letras, usando la función SUBSTR [9] cambiamos el valor de la columna last_name a una abreviatura.
CREAR O REEMPLAZAR EL DISPARADOR phone_book_insert ANTES DE INSERTAR EN phone_book PARA CADA FILA CUANDO ( LONGITUD ( nuevo . apellido ) > 10 ) COMIENZO : nuevo . apellido : = SUBSTR (: nuevo . apellido , 0 , 1 ); FIN ; Ahora realizando un INSERT de alguien con un nombre grande.
INSERT INTO phone_book VALUES ( 6 , 'MuyMuyLargoApellido' , 'Erin' , 'Minneapolis' , 'MN' , '989 University Drive' , '123-222-4456' , 55408 , TO_DATE ( '21/11/1991' , ' DD/MM/AAAA' )); El disparador funcionó según el resultado anterior, modificando el valor de INSERT antes de que se ejecutara.
El uso de un activador de instrucción BEFORE es particularmente útil cuando se aplican restricciones de bases de datos. [10] Este ejemplo demuestra cómo imponer una restricción a alguien llamado "ALGÚN USUARIO" en la tabla list_teléfono.
CREAR O REEMPLAZAR EL DISPARADOR hauschbc ANTES DE INSERTAR EN ALGÚN USUARIO . agenda_telefónica BEGIN RAISE_APPLICATION_ERROR ( num => - 20050 , msg => 'Aquí va el mensaje de error' ); FIN ; Ahora, cuando "ALGUNO USUARIO" inicie 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érica. Debido a los numerosos errores predefinidos, esta variable debe estar en el rango de −20000 a −20999.
[...] nivel de sistema se introdujeron en Oracle8i. [...] los disparadores a nivel del sistema se activan en eventos específicos del sistema, como inicio de sesión, cierre de sesión, inicio de la base de datos, ejecución de DDL y error del servidor [...].