Disparadores SQL: La guía esencial

Los disparadores son entidades de base de datos en SQL Server. Técnicamente, son una clase particular de llamadas a funciones que responden a operaciones específicas de la base de datos.

Esta guía esencial le proporcionará información en profundidad sobre los desencadenadores SQL que puede resultarle muy útil en su profesión. Empecemos

¿Qué son los disparadores SQL?

La palabra «disparador» describe una sentencia que un servidor ejecuta automáticamente cada vez que se altera el contenido de la base de datos.

Un disparador es un grupo de consultas SQL con un nombre especial que se almacenan en el espacio de memoria. Es un tipo específico de llamada a una función que se invoca inmediatamente cada vez que se produce cualquier evento en la base de datos. Cada disparador tiene asignada una tabla.

Por ejemplo, un disparador podría activarse cada vez que se añade una nueva columna a una tabla concreta o si se alteran registros específicos.

Según Microsoft Developer Network, los disparadores son una clase particular de procedimientos almacenados. En una sentencia trigger, definimos inicialmente cuándo debe ejecutarse el trigger y, a continuación, proporcionamos la acción que debe llevarse a cabo una vez activado el trigger.

Sintaxis:

CREAR DISPARADOR nombre_disparador
ANTES/ DESPUÉS
INSERTAR/ACTUALIZAR/BORRAR
ON nombre_tabla
FOR EACH ROW SET operation [trigger_body];

Explicación de cada parámetro

• CREATE TRIGGER nombre_disparador – Se utiliza para construir un disparador o para cambiar el nombre de un disparador existente.
• BEFORE/AFTER – Se utiliza para definir el tiempo de ejecución del trigger ( antes o después de un determinado evento).
• INSERTAR/ACTUALIZAR/ELIMINAR – Aquí se describe la acción que deseamos realizar sobre las tablas.
• ON tableName – Aquí definimos el nombre de la tabla para establecer un disparador.
• FOR EACH ROW – Esta declaración se refiere al disparador de filas, lo que significa que los disparadores se ejecutarán cada vez que se modifique una fila.
• trigger_body – Especifica la acción que se llevará a cabo cuando se active el disparador.

Los disparadores son funciones almacenadas con identidades distintivas que nos permiten reutilizar consultas que ya han sido ejecutadas y almacenadas de forma segura en la memoria. Ahora vamos a intentar comprender por qué SQL los necesita.

Los desencadenantes se utilizan sobre todo para regular el despliegue de código cada vez que se produce un evento. Dicho de otro modo, utilizar disparadores es la opción ideal si necesita que un fragmento de código específico se ejecute constantemente en respuesta a un evento concreto.

Las siguientes son algunas de las ventajas de utilizar triggers en las operaciones de bases de datos SQL.

1. Realiza comprobaciones adicionales al insertar, actualizar o eliminar datos de la tabla afectada.
2. Reduce los tiempos de respuesta, lo que ayuda a aumentar los gastos informáticos.
3. Permite la codificación de sofisticados parámetros por defecto inaccesibles por las restricciones iniciales.

La integridad referencial es una propiedad fundamental de los sistemas de bases de datos relacionales. Significa que los datos conservados en el sistema de base de datos deben ser siempre exactos para cada transacción y cada operación.

Si dos tablas se encuentran en bases de datos o sistemas separados, no hay forma de garantizar la validación de los datos dentro de ellas mediante valores de restricción. En tal situación, los desencadenantes son la única opción de ejecución.

Combinación de argumentos de desencadenantes

Para cada tabla, podemos especificar seis tipos de desencadenadores diferentes. Estas son las combinaciones de argumentos de desencadenador incluidas en los desencadenadores SQL a nivel de fila.

ANTES DE INSERTAR: Estos disparadores ejecutan la acción sobre las filas antes de realizar cualquier operación de INSERT en la tabla especificada o en la base de datos.

DESPUÉS DE INSERTAR: Ejecuta la acción sobre las filas inmediatamente después de cualquier actividad INSERT de la base de datos.

ANTES DE ACTUALIZAR: Con estos desencadenantes, se ejecuta una función sobre las filas antes de realizar una acción de ACTUALIZACIÓN sobre la base de datos.

DESPUÉS DE ACTUALIZAR: Ejecuta la acción sobre las filas inmediatamente después de cualquier actividad de ACTUALIZAR de la base de datos o de una tabla específica.

ANTES DE ELIMINAR: Ejecuta una determinada operación sobre las filas incluso antes de que la base de datos o la tabla se someta a una acción de ELIMINAR.

DESPUÉS DE ELIMINAR: Estos disparadores ejecutan la acción sobre las filas después de cada operación de ELIMINACIÓN.

Tipos de disparadores SQL

Los disparadores SQL son funciones almacenadas que se ejecutan inmediatamente cuando se producen eventos específicos. Se asemeja a la programación basada en eventos. Las situaciones posteriores pueden iniciar la ejecución de los triggers.

DisparadoresDML – DML significa Lenguaje de Manipulación de Datos. La ejecución de código como reacción a la modificación de datos es posible mediante los disparadores DML. Estos disparadores se activan cuando se ejecutan comandos DML como INSERT, UPDATE y DELETE. También se denominan «Disparadores a nivel de tabla».

DisparadoresDDL – DDL significa Lenguaje de Definición de Datos. Los disparadores DDL nos permiten ejecutar código como reacción a cambios en el esquema de la base de datos, como añadir o eliminar tablas, o a eventos del servidor, como cuando un usuario se registra. Se denominan «Disparadores a nivel de base de datos».

Estos Disparadores pueden ser activados, Cuando ciertas sentencias DDL como CREATE, ALTER, o DROP son ejecutadas en la base de datos activa. Estos también pueden ser utilizados para vigilar y gestionar las actividades que se ejecutan.

Disparadores de inicio de sesión – Cada vez que se produce cualquier evento LOGON (inicio, inicio de sesión, cierre de sesión, apagado), se invocan inmediatamente los disparadores de inicio de sesión. Sólo se llevan a cabo tras un proceso de autenticación del usuario, incluso antes de que se inicie la transacción del usuario. Los disparadores LOGON no se activarán si falla la autorización.

Estos disparadores pueden utilizarse para registrar el historial de inicios de sesión o establecer una restricción de eventos para un inicio de sesión concreto, entre otras funciones de auditoría y gestión de identidades para conexiones de servidor.

DisparadoresCLR – CLR son las siglas de Common Language Runtime. Los disparadores CLR son, de hecho, un subconjunto único de disparadores construidos principalmente sobre CLR dentro de la tecnología .NET. Estos disparadores son útiles si el disparador necesita realizar muchos cálculos o necesita relacionarse con una entidad que no sea SQL.

En efecto, los disparadores DML y DDL pueden construirse habilitando la codificación de disparadores CLR compatibles en tecnologías .NET, como Visual Basic, C# y F-sharp.

Ejemplo de disparador de SQL Server

Comprendamos estos conceptos de trigger con un ejemplo.

En primer lugar, creemos una base de datos utilizando sentencias SQL.

CREAR BASE DE DATOS testdb;
use testdb;

Aquí he dado «testdb» como nombre de la base de datos. Y el siguiente paso es crear una tabla.

CREAR TABLA student(
  nombre varchar(25),
  id int(2),
  matemáticas int(2),
  física int(2),
  biología int(2)
  social int(2),
  total int(2)
 );

He creado una tabla para almacenar los datos de los alumnos. Y aquí está el comando para describir la estructura de la tabla. Aquí «alumno» es el nombre de la tabla que le he dado.

DESC alumno;

A continuación se muestra la estructura de la tabla que he creado.

 --------- ------------- ------ ----- --------- ------- 
| Campo | Tipo | Nulo | Clave | Predeterminado | Extra
 --------- ------------- ------ ----- --------- ------- 
| name | varchar(25) | YES | | NULL | | NULL
| id | int | YES | | NULL | | id
| matemáticas int SI NULL
| física | int | SÍ | NULL | 
| biología | int | SÍ | NULL | | social
| ciencias sociales int SI NULL
| total | int | SÍ | NULL | | | | | biología
 --------- ------------- ------ ----- --------- ------- 
7 filas en el conjunto (0.00 seg)

Después de crear una tabla, el siguiente paso es configurar un disparador. Vamos a probar a utilizar el argumento BEFORE INSERT.

El nombre del disparador que he creado es «marcas». En cuanto se modifique la tabla con las notas del alumno, el desencadenador que aparece a continuación intentará determinar automáticamente la nota global del alumno.

CREAR DISPARADOR marcas
ANTES DE INSERTAR
EN
estudiante
PARA CADA FILA
set nuevo.total=nuevo.matemáticas nuevo.física nuevo.biología nuevo.social;

Dado que debemos sustituir los datos de las filas en lugar de trabajar con los antiguos, hemos definido «total» utilizando un nuevo nombre de clase, y todas las expresiones posteriores llevan como prefijo nuevas palabras clave después de total utilizando el operador de punto. Ahora, añadiremos valores a cada fila y veremos los resultados. Inicialmente, la nota total es 0 para cada alumno.

INSERT INTO alumno VALUES("George",02,99,87,92,91,0);
INSERT INTO alumno VALUES("James",03,91,81,94,90,0);
INSERT INTO student VALUES("Harry",04,86,70,73,88,0);
INSERT INTO alumno VALUES("John",05,73,89,78,92,0);
INSERT INTO student VALUES("Lisa",01,94,75,69,79,0);

En este caso, la sentencia trigger se activará automáticamente cuando se inserten los datos en la tabla student. El desencadenante calculará las notas totales de cada estudiante. Ahora, veamos si el disparador se invoca o no utilizando una sentencia SELECT.

SELECT * FROM nombre_tabla;

Y aquí está la salida final

mysql> select * from alumno;
 -------- ------ ------- --------- --------- -------- ------- 
| Nombre Id Matemáticas Física Biología Social Total
 -------- ------ ------- --------- --------- -------- ------- 
| George 2 91 81 94 90 356
| James 3 86 70 73 88 317
| Harry 4 73 89 78 92 332
| John 5 94 75 69 79 317
| Lisa 1 99 87 92 91 369
 -------- ------ ------- --------- --------- -------- ------- 
5 filas en el conjunto (0,00 seg)

En el resultado anterior, puede ver que todas las notas de las asignaturas se añaden automáticamente para cada alumno. Por lo tanto, podemos concluir que el disparador se invocó con éxito.

Operaciones adicionales del disparador

Podemos realizar muchas operaciones utilizando disparadores. Algunas pueden ser simples y otras un poco complejas, pero una vez que repasamos las consultas es fácil de entender. Empleando sentencias Transact-SQL, puede activar, desactivar o eliminar los disparadores utilizando los siguientes comandos.

Consulta para comprobar si existe o no un disparador específico

Este comando comprueba si existe el disparador especificado en toda la base de datos.

SELECT * FROM [sys].[triggers] WHERE [name] = 'Nombre_desencadenante'

Consulta para mostrar disparadores

Todos los disparadores disponibles en la base de datos activa se mostrarán mediante la siguiente sentencia.

MOSTRAR DISPARADORES;

Consulta para desactivar el disparador

El siguiente comando desactiva el disparador en la base de datos activa.

DISABLE TRIGGER trigger_name ON DATABASE;

También puede especificar un nombre de tabla determinado para desactivar un disparador.

DISABLE TRIGGER trigger_name ON table_name;

Consulta para activar el disparador

El siguiente comando desactiva primero un desencadenador específico que se definió en la tabla especificada de la base de datos activa antes de volver a activarlo.

ALTER TABLE nombre_tabla DISABLE TRIGGER nombre_disparador

ALTER TABLE table_name ENABLE TRIGGER trigger_name

El trigger debe estar deshabilitado, antes de intentar habilitarlo,

Consulta para habilitar o deshabilitar todos los disparadores de una tabla

Utilizando la sentencia SQL anterior, podemos desactivar o activar todos los desencadenadores de la tabla a la vez sustituyendo «ALL» en lugar de un nombre de desencadenador específico.

ALTER TABLE nombre_tabla DISABLE TRIGGER ALL 

ALTER TABLE nombre_tabla ENABLE TRIGGER ALL

Consulta para borrar o eliminar un disparador

Un trigger puede eliminarse borrándolo o borrando toda la tabla. Todos los disparadores relacionados también se eliminan cuando se borra una tabla.

DROP TRIGGER [nombre_disparador];

Siempre que se elimina un disparador, se eliminan los datos relacionados de la tabla de datos sys.objects.

Ventajas de los desencadenadores

• Es sencillo crear disparadores y el propio disparador puede invocar funciones y métodos almacenados.
• Los usuarios pueden implementar auditorías sencillas utilizando desencadenadores.
• Trágicamente, no se pueden crear restricciones entre entidades en sistemas de bases de datos con SQL Server, aunque se puede emular el funcionamiento de las restricciones utilizando disparadores.
• Las restricciones de integridad pueden implementarse a través de bases de datos utilizando disparadores.
• Cuando se requiere una validación de grupo en lugar de una verificación fila por fila de los datos recién introducidos o modificados, los desencadenantes pueden resultar útiles.

Desventajas de los triggers

Los desencadenadores SQL pueden no ser la mejor opción en algunas situaciones debido a sus limitaciones.

• Los desencadenadores deben documentarse con precisión.
• Debido a la ejecución simultánea de bases de datos que pueden no ser accesibles a los componentes de la aplicación, los triggers pueden ser difíciles de depurar.
• Las sentencias DML se vuelven más complejas cuando se utilizan disparadores.
• Incluso un problema menor del disparador tiene el potencial de provocar fallos lógicos en la sentencia.

Conclusión

Los desencadenadores son componentes muy útiles de Transact-SQL y SQL, y también puede utilizarlos en Oracle. El uso de disparadores es crucial a la hora de llamar a métodos almacenados. Estos desencadenadores SQL nos permiten analizar los plazos de actividad y determinar cómo responder a ellos en caso necesario. También podemos buscar una tabla determinada que esté conectada a un desencadenador para obtener datos.

Los desencadenantes pueden habilitar la recursión. Cada vez que un desencadenante en una tabla ejecuta un comando en la tabla padre, se activa la segunda iteración del desencadenante, lo que se conoce como desencadenante recursivo. Esto ayuda cuando se intenta resolver una correlación de identidades.

Además, los disparadores regulan el patrón de actualización que la base de datos está autorizada a aceptar. Es muy beneficioso mantener las restricciones de integridad de los datos en el sistema de base de datos si no existen claves de restricción SQL principalmente la clave primaria y la clave foránea.

Espero que este artículo le haya resultado útil para aprender sobre los desencadenadores SQL.

Si desea aprender bases de datos en profundidad, aquí tiene algunos recursos excelentes para aprender SQL y NoSQL.