Disparador de base de datos

Además de los disparadores estándar del lenguaje de manipulación de datos (DML) que ejecutan código PL/SQL cuando se modifican los datos, Oracle admite disparadores que se activan ante cambios a nivel de esquema y eventos de base de datos a nivel de sistema. Tanto los disparadores del lenguaje de definición de datos (DDL) a nivel de esquema como los disparadores de eventos del sistema se introdujeron conjuntamente en Oracle 8i . ^{[ 2 ] [ 3 ]}

Microsoft SQL Server admite desencadenadores para instrucciones DML y DDL, así como un desencadenador especial de "inicio de sesión".

El alcance de un desencadenador DDL puede ser una sola base de datos CREATE TRIGGER...ON DATABASEo toda la instancia de SQL Server CREATE TRIGGER...ON ALL SERVER. Cuando se configura para toda la instancia, el desencadenador captura los eventos tanto del servidor como de la base de datos que se ejecutan dentro de esa instancia.

Una lista completa de los eventos de activación de desencadenadores DDL disponibles está disponible en la documentación oficial de Microsoft . ^{[ 5 ]}

Los desencadenadores DDL de SQL Server pueden abarcar una sola base de datos o una instancia completa. Para las operaciones DML, insertedse deletedutilizan pseudotablas, y aunque los desencadenadores operan a nivel de instrucción, la lógica a nivel de fila se puede lograr mediante un cursor.

PostgreSQL agregó soporte para disparadores en 1997, incluyendo posteriormente disparadores específicos de columna en la versión 9.0 en total conformidad con el estándar SQL:2003 . ^{[ 6 ]} Mientras que los disparadores DML estándar están ligados a tablas específicas, las modificaciones de esquema que no son DML, como CREATE TABLE—, se capturan globalmente a nivel de base de datos mediante disparadores de eventos, una característica introducida en PostgreSQL 9.3. ^{[ 7 ]}

La sintaxis SQL básica para definir un disparador en PostgreSQL sigue esta estructura:

CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] } ON table_name [ FOR [ EACH ] { ROW | STATEMENT } ] EXECUTE PROCEDURE funcname ( arguments )

Firebird admite múltiples disparadores a nivel de fila por tabla para operaciones de actualización INSERT, modificación y cualquier combinación de estas acciones. Estos se ejecutan antes o después de las modificaciones de datos y funcionan además de los cambios de tabla predeterminados. Los usuarios pueden especificar el orden de ejecución de varios disparadores entre sí mediante la cláusula. Los disparadores también se pueden definir en vistas como disparadores, que reemplazan la lógica de vista actualizable predeterminada. Antes de la versión 2.1, los disparadores en vistas actualizables se ejecutaban además de la lógica del motor predeterminada en lugar de reemplazarla. ^{[ 8 ]}UPDATEDELETEPOSITIONINSTEAD OF

El motor de base de datos permite que los disparadores se aniden y recursen de forma predeterminada, y no genera excepciones de mutación de tabla. Firebird utiliza las variables de contexto NEWy OLDpara acceder a los estados de las filas durante las modificaciones de datos. También proporciona los indicadores de contexto booleanos INSERTING, UPDATINGy DELETINGpara identificar programáticamente el evento DML específico que inició la ejecución del disparador.

{ CREAR | RECREAR | CREAR O MODIFICAR } DISPARADOR 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 INICIO .... FIN

A partir de la versión 2.1, Firebird introdujo soporte para disparadores a nivel de base de datos, que se ejecutan durante eventos específicos de sesión y transacción. Estos incluyen eventos de conexión, mediante disparadores CONNECTy DISCONNECT, y eventos transaccionales, mediante disparadores TRANSACTION START, TRANSACTION COMMIT, y TRANSACTION ROLLBACK. Si se produce una excepción durante un CONNECTdisparador, la secuencia de conexión se interrumpe. Del mismo modo, las excepciones que se producen dentro de un TRANSACTION COMMITdisparador bloquean la finalización de la transacción, incluida la fase de preparación durante una confirmación en dos fases.

Los disparadores a nivel de base de datos se pueden utilizar para aplicar restricciones de varias tablas o para emular vistas materializadas . Cuando se produce una excepción dentro de un TRANSACTION COMMITdisparador, las modificaciones transaccionales realizadas por el disparador antes del error se revierten y se notifica a la aplicación cliente. Sin embargo, la transacción en sí permanece activa como si la COMMIToperación nunca se hubiera iniciado, lo que permite a la aplicación realizar modificaciones de datos adicionales y solicitar la secuencia de confirmación. ^{[ 9 ]}

La sintaxis general para crear o modificar disparadores a nivel de base de datos en Firebird utiliza la siguiente estructura:

{ CREAR | RECREAR | CREAR O MODIFICAR } DISPARADOR nombre [ ACTIVO | INACTIVO ] EN { CONECTAR | DESCONECTAR | INICIO DE TRANSACCIÓN | CONFIRMAR TRANSACCIÓN | REVERTIR TRANSACCIÓN } [ POSICIÓN n ] COMO INICIO ..... FIN

Los disparadores de MySQL/MariaDB, introducidos en 2005 (v5.0), admiten eventos DML, y MySQL 8.0 permite múltiples disparadores del mismo tipo, aunque no admite disparadores DDL. ^{[ 10 ]} La sintaxis, compatible con ambos sistemas, utiliza CREATE TRIGGER, DROP TRIGGER, y FOR EACH ROWpara acciones a nivel de fila, con cuerpos que comienzan con SETo BEGIN. ^{[ 11 ]}

IBM Db2 para Linux , Unix y Windows (Db2 para LUW) admite tres tipos principales de disparadores clasificados por tiempo de activación: BEFORE, AFTER, y INSTEAD OF. El motor admite granularidades tanto a nivel de fila como a nivel de sentencia. Si se definen varios disparadores para la misma operación en una sola tabla, su orden de ejecución se determina secuencialmente por su marca de tiempo de creación. A partir de la versión 9.7, Db2 también admite transacciones autónomas, lo que permite que un disparador ejecute un trabajo transaccional independiente que se puede confirmar o revertir sin afectar la sentencia que lo activó. ^{[ 12 ]}

Un BEFOREdisparador se utiliza para validar los datos entrantes y determinar si se debe permitir una modificación de datos. Si se produce una excepción dentro de un BEFOREdisparador, la operación se aborta y los cambios en la base de datos se revierten. Si bien BEFORElos disparadores no pueden ejecutar instrucciones DML para modificar directamente las tablas de la base de datos, no son completamente de solo lectura; pueden modificar programáticamente los estados de los datos entrantes alterando los valores de destino dentro de las variables de transición de la fila antes de que se escriban en el disco. ^{[ 13 ]}

Por el contrario, AFTERlos disparadores se ejecutan tras completar la modificación de datos solicitada. A diferencia de BEFORElos disparadores, AFTERlos disparadores son totalmente capaces de ejecutar operaciones de escritura DML posteriores en cualquier tabla de la base de datos, incluida la tabla activa que inició la ejecución del disparador. INSTEAD OFLos disparadores se utilizan exclusivamente en vistas para anular la lógica de procesamiento predeterminada y hacer que las vistas no actualizables sean modificables. La lógica procedimental dentro de los disparadores de Db2 se escribe normalmente utilizando la extensión del lenguaje procedimental SQL PL .

CREATE [ TEMP | TEMPORARY ] TRIGGER [ IF NOT EXISTS ] [ database_name .] trigger_name [ BEFORE | AFTER | INSTEAD OF ] { DELETE | INSERT | UPDATE [ OF column_name [, column_name ]...] } ON { table_name | view_name } [ FOR EACH ROW ] [ WHEN condition ] BEGIN ... END

SQLite solo admite disparadores a nivel de fila y no ofrece soporte nativo para disparadores a nivel de sentencia.

Las vistas actualizables, que tampoco son compatibles de forma predeterminada con el motor, pueden emularse implementando INSTEAD OFactivadores sobre las estructuras de vista de destino.

Los disparadores se implementan en sistemas de gestión de bases de datos no relacionales. Por ejemplo, la base de datos XML Sedna admite disparadores que utilizan XQuery . Los disparadores en Sedna están diseñados para imitar el comportamiento estructural de los disparadores estándar SQL:2003 , pero están optimizados de forma nativa para paradigmas de consulta y actualización XML, incluyendo XPath , XQuery y extensiones de actualización XML.

En Sedna, se puede asignar un disparador a nodos específicos dentro de un documento XML almacenado en la base de datos. Cuando estos nodos se modifican, el disparador ejecuta automáticamente las consultas XQuery o los scripts de actualización correspondientes definidos en su cuerpo de acción. Por ejemplo, el siguiente disparador bloquea la eliminación de un personnodo si alguna subasta abierta sigue haciendo referencia a ese perfil específico:

CREATE TRIGGER "trigger3" BEFORE DELETE ON doc( "auction" )/ site // person FOR EACH NODE DO { if ( exists ( $ WHERE // open_auction / bidder / personref / @person = $ OLD / @id )) then ( ) else $ OLD ; }

En MongoDB Atlas, los disparadores se implementan como parte de una arquitectura sin servidor y basada en eventos para ejecutar lógica automatizada del lado del servidor cuando ocurren cambios en los datos. A diferencia de los disparadores de base de datos tradicionales, que se ejecutan directamente en el servidor de base de datos, los disparadores de base de datos de MongoDB Atlas operan en una capa de computación de escalado independiente. Monitorean las modificaciones de datos en tiempo real ( INSERT, UPDATE, REPLACE, o DELETE) usando Change Streams. ^{[ 14 ]} Cuando se detecta un evento, el disparador ejecuta automáticamente una función Atlas personalizada (escrita en JavaScript) o envía el evento a un servicio externo. Esta funcionalidad se utiliza principalmente para garantizar la consistencia e integridad de los datos, y para implementar procesos de auditoría automáticamente en todo el ecosistema de datos.

Una tienda en línea que realiza el seguimiento de las entregas de paquetes puede usar un disparador de base de datos para notificar a los clientes cada vez que sus envíos cambien de ubicación. El disparador monitorea los cambios de datos dentro de la store.orderscolección, donde cada pedido activo se almacena como un documento estructurado como el siguiente ejemplo:

{ _id : Object Id ( " 59cf1860a95168b8f685e378" ) , customerId : Object Id ( " 59cf17e1a95168b8f685e377" ) , orderDate : ISODate ( " 2018-06-26T16 :20:42.313Z" ) , shipDate : ISODate ( "2018-06-27T08:20:23.311Z" ) , orderContents : [ { qty : 1 , name : " Earl Grey Tea Bags - 100ct " , price : NumberDecimal ( " 10.99 " ) } ] , shippingLocation : [ { loca ción : " Memphis" , hora : ISODate ( " 2018-06-27T18 :22:33.243Z" ) } , ] }

Para comprender cómo funcionan los disparadores de base de datos, es importante analizar dos conceptos clave: la granularidad del disparador (a nivel de fila o a nivel de instrucción) y el momento de ejecución (ANTES o DESPUÉS). Estos ajustes determinan cuántas veces se ejecuta un disparador y cuándo se ejecuta exactamente su código.

La principal diferencia entre estos dos tipos de activadores radica en la frecuencia con la que se ejecutan cuando se modifican los datos:

• Los disparadores a nivel de fila se ejecutan una vez por cada fila afectada por un comando de base de datos, como una instrucción INSERT, UPDATE, o . Por ejemplo, si un comando modifica 50 filas en una tabla, un disparador a nivel de fila se ejecutará 50 veces. Si el comando no coincide con ninguna fila y no modifica nada, el disparador a nivel de fila no se ejecutará en absoluto.DELETEUPDATE
• Los disparadores a nivel de sentencia se ejecutan exactamente una vez para toda la sentencia SQL, independientemente de cuántas filas se modifiquen. Incluso si el comando de la base de datos no afecta a ninguna fila, un disparador a nivel de sentencia ejecutará su código una sola vez.

La granularidad de ejecución de los disparadores varía según el motor. Sistemas como PostgreSQL utilizan FOR EACH ROWdisparadores a nivel de fila, mientras que Microsoft SQL Server, por defecto, ejecuta los disparadores a nivel de sentencia y requiere el manejo de filas mediante pseudotablas. Los desarrolladores deben evitar la recursión de disparadores , donde un disparador actualiza una tabla que se activa a sí misma, lo que provoca bucles infinitos y posibles fallos en la base de datos.

Las opciones de temporización, especificadas mediante las palabras clave BEFOREy AFTER, determinan cuándo se ejecuta el disparador en relación con la modificación de los datos:

• BEFORELos disparadores ejecutan su código antes de que los cambios de datos se guarden permanentemente en la tabla. Un uso común de los BEFOREdisparadores es la validación de datos de entrada o las comprobaciones de seguridad. Por ejemplo, un BEFOREdisparador puede revisar un registro entrante y corregir valores incorrectos o detener la operación por completo antes de que se escriban datos en el disco.
• AFTERLos disparadores ejecutan su código después de que los cambios de datos se hayan escrito de forma segura en la tabla. Estos disparadores se utilizan normalmente para tareas de posprocesamiento, como registrar información histórica o crear registros de auditoría. Si bien BEFORElos disparadores son más adecuados para verificar y limpiar los datos entrantes, AFTERson ideales para copiar los cambios confirmados a tablas de historial o registro separadas. ^{[ 15 ]}

El siguiente ejemplo muestra un disparador de nivel de fila de Oracle PL/SQL configurado para ejecutarse después de una UPDATEoperación en una tabla llamada phone_book. Este disparador registra automáticamente los cambios históricos escribiendo una entrada de registro en una tabla de seguimiento separada llamada phone_book_audit. También utiliza una secuencia de base de datos llamada audit_id_sequencepara generar un número de identificación único para cada nueva entrada de registro:

CREATE OR REPLACE TRIGGER phone_book_audit AFTER UPDATE ON phone_book FOR EACH ROW BEGIN INSERT INTO phone_book_audit ( audit_id , audit_change , audit_l_name , audit_f_name , audit_old_phone_number , audit_new_phone_number , audit_date ) VALUES ( audit_id_sequence . nextVal , 'Update' , : OLD . last_name , : OLD . first_name , : OLD . phone_number , : NEW . phone_number , SYSDATE ); END ;

Un UPDATEcomando ejecutado en la phone_booktabla modifica los registros donde el apellido es "Jones":

ACTUALIZAR agenda telefónica ESTABLECER número_de_teléfono = '111-111-1111' DONDE apellido = 'Jones' ;

La tabla resultante phone_book_auditse rellena con dos entradas distintas porque la base de datos de origen contiene dos registros que coinciden con el apellido "Jones". Dado que un disparador a nivel de fila se ejecuta una vez por cada fila modificada, el motor invoca la lógica del disparador dos veces durante la ejecución de una única instrucción de actualización. ^{[ 16 ]}

Por el contrario, un AFTERdisparador a nivel de sentencia se ejecuta exactamente una vez por cada finalización de la sentencia, independientemente del número total de filas modificadas por el comando. La siguiente sintaxis de Oracle define un disparador a nivel de sentencia que registra un único evento de transacción dentro de la phone_book_edit_historytabla cada vez que UPDATEse produce una operación en phone_bookella:

CREATE OR REPLACE TRIGGER phone_book_history AFTER UPDATE ON phone_book BEGIN INSERT INTO phone_book_edit_history ( audit_history_id , username , modification , edit_date ) VALUES ( audit_history_id_sequence . nextVal , USER , 'Update' , SYSDATE ); END ;

La ejecución de la misma UPDATEinstrucción aísla el contraste de comportamiento de un desencadenante a nivel de instrucción:

ACTUALIZAR agenda telefónica ESTABLECER número_de_teléfono = '111-111-1111' DONDE apellido = 'Jones' ;

El registro de transacciones indica que el disparador se ejecutó solo una vez, a pesar de que la modificación de datos afectó a dos registros individuales.

Un BEFOREdisparador a nivel de fila evalúa y modifica los valores de datos entrantes antes de que la operación de escritura física se complete en el disco. Esta variación demuestra un disparador que utiliza una WHENrestricción de cláusula para interceptar una INSERTacción. Cuando un registro contiene una cadena de apellido con una longitud superior a 10 caracteres, el motor invoca la SUBSTRfunción ^{[ 17 ]} para truncar el valor de entrada a su carácter inicial: ^{[ 18 ]}

CREATE OR REPLACE TRIGGER phone_book_insert BEFORE INSERT ON phone_book FOR EACH ROW WHEN ( LENGTH ( new . last_name ) > 10 ) BEGIN : new . last_name : = SUBSTR (: new . last_name , 0 , 1 ); END ;

Este mecanismo se observa al intentar insertar un registro que cumpla con los criterios de filtro:

INSERT INTO phone_book VALUES ( 6 , 'VeryVeryLongLastName' , 'Erin' , 'Minneapolis' , 'MN' , '989 University Drive' , '123-222-4456' , 55408 , TO_DATE ( '11/21/1991' , 'MM/DD/YYYY' ));

El motor guarda los datos de la fila con la variable de apellido truncada, lo que confirma que la modificación se produjo antes de que el registro se escribiera en la tabla.

La implementación de un BEFOREdisparador a nivel de sentencia es un patrón de diseño estándar para aplicar parámetros de seguridad o restricciones de reglas de negocio de forma global en un recurso de base de datos. ^{[ 19 ]} La siguiente definición restringe el acceso a la base de datos en una tabla de destino bajo un esquema específico:

CREATE OR REPLACE TRIGGER hauschbc BEFORE INSERT ON SOMEUSER . phone_book BEGIN RAISE_APPLICATION_ERROR ( num => - 20050 , msg => 'Aquí va el mensaje de error.' ); END ;

Un intento de inserción de datos dirigido al recurso protegido provoca que el motor de la base de datos aborte la secuencia de transacciones y devuelva un mensaje de error a la interfaz de la aplicación cliente que realiza la llamada:

Error SQL: ORA-20050: El mensaje de error va aquí.

Las excepciones de tiempo de ejecución personalizadas generadas a través del RAISE_APPLICATION_ERRORprotocolo están sujetas a un rango numérico restringido. Para evitar conflictos con los códigos de error del sistema preasignados, el desarrollador de la aplicación debe definir esta variable dentro del rango numérico de -20000 a -20999.