Los SQL Triggers son otro poderoso objeto de base de datos que tenemos a nuestra disposición. En artículos anteriores, hemos cubierto las funciones definidas por el usuario, los procedimientos definidos por el usuario y las Vistas SQL. Hoy hablaremos de los triggers SQL y de cómo utilizarlos para conseguir el comportamiento deseado.
El modelo
Antes de pasar al tema de este artículo, echemos un vistazo rápido al modelo que estamos utilizando en este artículo pero también a lo largo de esta serie.
En este artículo, nos centraremos en los disparadores DML (lenguaje de manipulación de datos) y mostraremos cómo funcionan cuando realizamos cambios en una única tabla.
¿Qué son los disparadores SQL?
En SQL Server, los triggers son objetos de la base de datos, en realidad, un tipo especial de procedimiento almacenado, que «reacciona» a ciertas acciones que realizamos en la base de datos. La idea principal de los triggers es que siempre realizan una acción en caso de que ocurra algún evento. Si hablamos de triggers DML, estos cambios serán cambios en nuestros datos. Examinemos algunas situaciones interesantes:
- En caso de que realices una inserción en la tabla de llamadas, quieres actualizar que el cliente relacionado tiene 1 llamada más (en ese caso, deberíamos tener el atributo integer en la tabla de clientes)
- Cuando completes una llamada (actualizar llamada.valor del atributo end_time) se quiere aumentar el contador de llamadas realizadas por ese empleado durante ese día (de nuevo, deberíamos tener dicho atributo en la tabla de empleados)
- Cuando se intenta eliminar un empleado, se quiere comprobar si tiene llamadas relacionadas. Si es así, impedirá ese borrado y lanzará una excepción personalizada
- DML (lenguaje de manipulación de datos) triggers – Ya los hemos mencionado, y reaccionan a los comandos DML. Estos son – INSERT, UPDATE y DELETE
- Triggers DDL (lenguaje de definición de datos) – Como era de esperar, los triggers de este tipo reaccionarán a los comandos DDL como – CREATE, ALTER y DROP
- Triggers de inicio de sesión – El nombre lo dice todo. Este tipo reacciona a los eventos LOGON
- Un conjunto de {sql_statements} que se ejecutarán cuando se dispare el trigger (definido por los parámetros restantes)
- Debemos definir cuándo se dispara el trigger. Eso es lo que hace la parte {FOR | AFTER | INSTEAD OF}. Si nuestro trigger se define como FOR | AFTER | INSTEAD OF trigger entonces las sentencias SQL del trigger se ejecutarán después de que todas las acciones que dispararon este trigger se lancen con éxito. El trigger INSTEAD OF realizará controles y sustituirá la acción original por la acción del trigger, mientras que el trigger FOR | AFTER (significan lo mismo) ejecutará comandos adicionales después de que la sentencia original haya finalizado
- La parte { } denota qué comando dispara realmente este trigger. Debemos especificar al menos una opción, pero podríamos usar varias si lo necesitamos
- Comprobar (antes de la inserción) si todos los parámetros de la sentencia INSERT están bien, añadir algunos si es necesario, y realizar la inserción
- Después de la inserción, realizar tareas adicionales, como actualizar un valor en otra tabla
- Antes de la eliminación, comprobar si hay registros relacionados
- Actualizar ciertos valores (e.g. archivo de registro) después de la eliminación se hace
- La sentencia INSERT dispara esta consulta y en realidad se sustituye (INSTEAD OF INSERT) por la sentencia de este trigger
- Hemos definido una serie de variables locales para almacenar los valores del registro de inserción original (INSERTED). Este registro es específico para los triggers y permite acceder a este único registro y a sus valores
- Nota: El registro INSERTED puede utilizarse en los triggers SQL de inserción y actualización.
- Con las sentencias IF, hemos comprobado los valores y SET los valores si no estaban establecidos antes
- Al final de la consulta, realizamos la sentencia INSERT (la que sustituye a la original que disparó este trigger)
- Nota: Si el trigger está definido en una determinada tabla, para una determinada acción, se ejecutará siempre cuando se realice esta acción.
- Una vez más, realizamos la acción antes (en lugar de) de ejecutar realmente (INSTEAD OF DELETE)
- Hemos utilizado el registro DELETED. Este registro se puede utilizar en los triggers relacionados con la sentencia DELETE
- Nota: El registro DELETED se puede utilizar en los triggers SQL de borrado y actualización.
- Hemos utilizado la sentencia IF para determinar si la fila debe o no debe ser borrada. Si debe, hemos realizado la sentencia DELETE, y si no debe, se nos lanza una excepción
De los ejemplos, se puede observar que los triggers DML son acciones relacionadas con los comandos SQL definidos en estos triggers. Dado que son similares a los procedimientos almacenados, puede probar valores utilizando la sentencia IF, etc. Esto proporciona una gran flexibilidad.
La buena razón para utilizar los triggers DML SQL es el caso en el que se quiere asegurar que un determinado control se realizará antes o después de la sentencia definida en la tabla definida. Este podría ser el caso cuando su código está por todas partes, por ejemplo, la base de datos es utilizada por diferentes aplicaciones, el código está escrito directamente en las aplicaciones y no lo tiene bien documentado.
Tipos de SQL Triggers
En SQL Server, tenemos 3 grupos de triggers:
En este artículo, nos centraremos en los triggers DML, porque son los más utilizados. Cubriremos los dos tipos de trigger restantes en los próximos artículos de esta serie.
Triggers DML – Sintaxis
La sintaxis SQL simplificada para definir el trigger es la siguiente.
|
1
2
3
. 4
5
|
CREATE TRIGGER trigger_name
ON table_name
{FOR | AFTER | INSTEAD OF} { }
Como
{sql_statements}
|
La mayor parte de la sintaxis debería explicarse por sí misma. La idea principal es definir:
Con esto en mente, podemos escribir fácilmente disparadores que:
Si desea soltar un disparador, que va a utilizar:
|
1
|
DROP TRIGGER trigger_name;
|
Disparador SQL INSERT – Ejemplo
En primer lugar, crearemos un sencillo disparador SQL que realizará la comprobación antes de la sentencia INSERT.
|
1
2
3
5
. 6
7
8
9
10
11
|
DROP TRIGGER IF EXISTS t_country_insert;
GO
CREATE TRIGGER t_country_insert ON country INSTEAD OF INSERT
AS BEGIN
DECLARE @country_name CHAR(128);
DECLARE @nombre_país_es CHAR(128);
DECLARE @country_code CHAR(8);
SELECT @nombre_de_país = nombre_de_país, @nombre_de_país = nombre_de_país, @código_de_país = código_de_país FROM INSERTED;
IF @nombre_de_país ES NULL SET @nombre_de_país = @nombre_de_país;
IF @nombre_de_país ES NULL SET @nombre_de_país = @nombre_de_país;
INSERT INTO país (nombre_país, nombre_país_es, código_país) VALUES (@nombre_país, @nombre_país_es, @código_país);
END;
|
Podemos ver nuestro trigger en el Explorador de Objetos, cuando expandimos los datos de la tabla relacionada (país).
Quiero destacar algunas cosas aquí:
Ejecutamos ahora un comando INSERT INTO y vemos qué ocurre en la base de datos. Ejecutaremos las siguientes sentencias:
|
1
2
3
|
SELECT * FROM country;
INSERT INTO country (country_name_eng, country_code) VALUES (‘United Kingdom’, ‘UK’);
SELECT * FROM country;
|
El resultado está en la imagen de abajo.
Puedes notar fácilmente que la fila con id = 10, había sido insertada. No hemos especificado el nombre_de_país, pero el trigger ha hecho su trabajo y ha rellenado ese valor con nombre_de_país.
Disparador SQL DELETE – Ejemplo
Ahora vamos a crear un disparador que se disparará ante la sentencia DELETE en la tabla país.
|
1
2
3
4
5
. 6
7
8
9
10
11
13
|
DROP TRIGGER IF EXISTS t_country_delete;
GO
CREATE TRIGGER t_country_delete ON country INSTEAD OF DELETE
AS BEGIN
DECLARE @id INT;
DECLARE @count INT;
SELECT @id = id FROM DELETED;
SELECT @count = COUNT(*) FROM city WHERE country_id = @id;
IF @count = 0
DELETE FROM country WHERE id = @id;
ELSE
THROW 51000, ‘can not delete – country is referenced in other tables’, 1;
END;
|
Para este trigger, cabe destacar lo siguiente:
La ejecución de la siguiente sentencia ha ido sin error porque el país con id = 6 no tenía registros relacionados.
|
1
|
DELETE FROM country WHERE id = 6;
|
Si ejecutamos esta sentencia veremos un mensaje de error personalizado, como se muestra en la siguiente imagen.
|
1
|
DELETE FROM country WHERE id = 1;
|
Este mensaje no sólo es descriptivo, sino que nos permite tratar este error de forma agradable y mostrar un mensaje más significativo al usuario final.
Disparador SQL UPDATE
Este lo dejaré para ti, como práctica. Así que intenta escribir el trigger UPDATE. Lo importante que debes saber es que en el trigger de actualización puedes usar ambos – registros INSERTADOS (después de la actualización) y BORRADOS (antes de la actualización). En casi todos los casos, necesitarás utilizar ambos.
¿Cuándo utilizar los disparadores SQL?
Los disparadores tienen mucho en común con los procedimientos almacenados. Sin embargo, en comparación con los procedimientos almacenados son limitados en lo que puede hacer. Por lo tanto, prefiero tener un procedimiento almacenado para insertar/actualizar/borrar y hacer todas las comprobaciones y acciones adicionales allí.
Aún así, esa no es siempre la opción. Si has heredado un sistema o simplemente no quieres poner toda la lógica en los procedimientos almacenados, entonces los triggers podrían ser una solución para muchos problemas que puedas tener.
Tabla de contenidos
Aprende SQL: CREATE DATABASE & CREATE TABLE Operations
Aprende SQL: INSERT INTO TABLE
Aprender SQL: Clave primaria
Aprende SQL: Clave foránea
Aprender SQL: Sentencia SELECT
Aprende SQL: INNER JOIN vs LEFT JOIN
Aprende SQL: Scripts SQL
Aprende SQL: Tipos de relaciones
Aprender SQL: Unir varias tablas
Aprender SQL: Funciones agregadas
Aprender SQL: ¿Cómo escribir una consulta SELECT compleja?
Aprende SQL: La base de datos INFORMATION_SCHEMA
Aprende SQL: Tipos de Datos SQL
Aprende SQL: Teoría de conjuntos
Aprender SQL: Funciones definidas por el usuario
Aprender SQL: Procedimientos almacenados definidos por el usuario
Aprender SQL: Vistas SQL
Aprender SQL: SQL Triggers
Aprender SQL: Practicar consultas SQL
Aprender SQL: Ejemplos de consultas SQL
Aprender SQL: Crear un informe manualmente utilizando consultas SQL
Aprender SQL: Funciones de fecha y hora de SQL Server
Aprender SQL: Crear informes de SQL Server utilizando funciones de fecha y hora
Aprender SQL: Tablas dinámicas de SQL Server
Aprender SQL: Exportación de SQL Server a Excel
Aprender SQL: Introducción a los bucles de SQL Server
Aprender SQL: Cursores de SQL Server
Aprender SQL: SQL Best Practices for Deleting and Updating data
Aprender SQL: Convenciones de nomenclatura
Aprender SQL: Trabajos relacionados con SQL
Aprende SQL: Uniones no equis en SQL Server
Aprender SQL: Inyección SQL
Aprender SQL: SQL dinámico
