Tutorial de Triggers SQL con ejemplos sencillos

Vamos a ver qué son los TRIGGER de base de datos, como nos pueden ayudar a mantener la integridad de nuestra base de datos y como crearlos con SQL paso a paso.

¿Qué es un TRIGGER SQL?

Un TRIGGER, también conocido como disparador, es una especie de script de programación SQL para base de datos. Los TRIGGER son procedimientos que se ejecutarán según nuestras indicaciones cuando se realicen operaciones sobre la información de la base de datos. Estas operaciones pueden ser de actualización (UPDATE), inserción (INSERT) y borrado (DELETE).

Los TRIGGER además pueden modificar la información de la base de datos e incluso detener la ejecución de consultas erróneas. 

Los TRIGGER son procedimientos que se ejecutarán según nuestras indicaciones cuando se realicen operaciones sobre la información de la base de datos. Estas operaciones pueden ser de actualización (UPDATE), inserción (INSERT) y borrado (DELETE).

¿Para qué sirve un TRIGGER SQL?

Los TRIGGERS son una de las funcionalidades más útiles de las que disponemos cuando diseñamos y mantenemos bases de datos. Gracias a ellos podremos implementar ciertas características de nuestra base de datos sin necesidad de desarrollar programación en otros lenguajes externos. 

Imagínate por ejemplo que quieres duplicar en una segunda tabla toda la información que se inserte en otra. No existe ninguna forma de indicarle a la base de datos que lo realice de forma automática. Sin embargo, gracias a un TRIGGER que se ejecute tras un INSERT, podemos insertar esa información en la segunda tabla, todo esto sin que el usuario/programador que lanzó el INSERT tenga que hacer nada.

Las posibilidades de los TRIGGER son muy grandes, y siempre debemos tenerlos en mente como recurso para manipular la información de INSERT, UPDATE o DELETE.

Cómo crear TRIGGER SQL

Crear un TRIGGER con SQL es sencillo, casi todos los sistemas de bases de datos (SGBD) están preparados para ello, e incluso algunos pueden incorporar  un asistente que nos guie en el proceso. A continuación voy a explicarte como crearlo paso a paso, verás como en 5 minutos serás capaz de crear tu primer TRIGGER.

Estructura de un trigger

La creación de un TRIGGER sigue las bases de crear un procedimiento almacenado o una función almacenada. Primero declaramos un delimitador y usamos la sentencia CREATE:

DELIMITER $$
CREATE TRIGGER trigger_historico

En estas líneas SQL he creado un TRIGGER con el nombre trigger_historico. Este nombre me servirá después para identificarlo, pudiendo así borrarlo o buscarlo en la base de datos.

A continuación debemos indicar CUANDO DEBE EJECUTARSE el TRIGGER. Las indicaciones posibles en este punto son AFTER o BEFORE para indicar si  el disparador se ejecutará ANTES o DESPUÉS de la orden lanzada por el usuario. Estas órdenes serán: INSERT, DELETE o UPDATE. Por último indicaremos sobre que tabla actuará.

Vamos a completar el código anterior indicando que la ejecución será tras un INSERT en la tabla usuario:

DELIMITER $$
CREATE TRIGGER trigger_historico
AFTER INSERT ON usuario
FOR EACH ROW

Como puedes ver he añadido las dos últimas líneas: la primera indica que la ejecución será tras una inserción en la tabla usuario, mientras que la segunda forma parte de la estructura de un TRIGGER e indica que se debe aplicar a cada fila insertada.

Por último mediante el comando BEGIN y END indicamos las líneas de código SQL que ejecutará el TRIGGER:

DELIMITER $$
CREATE TRIGGER trigger_historico
AFTER INSERT ON usuario
FOR EACH ROW
BEGIN
//líneas de código SQL que se ejecutarán
END; $$

Datos nuevos y antiguos. El OLD y el NEW

Para que nuestras líneas de código SQL puedan acceder a la información que interviene en el TRIGGER, es decir, la información a la que afecta un UPDATE, INSERT o DELETE, disponemos de dos variables especiales NEW y OLD.

La variable NEW de un TRIGGER

NEW es la variable que almacena la nueva información que aporta la consulta a la base de datos, es decir, cada una de las filas que intervienen en un INSERT O UPDATE. Si por ejemplo se ha realizado un INSERT, gracias a NEW podremos acceder a los datos introducidos para cada columna de la tabla. NEW.nombre por ejemplo almacena la información de la columna nombre que tendrá el nuevo registro insertado en la tabla. 

Hay que tener en cuenta que NEW no estará disponible en todos los tipos de TRIGGER. En concreto los TRIGGER relacionados con un DELETE no dispondrán de información en esta variable ya que tan solo tendremos información antigua que es eliminada.

La variable OLD de un TRIGGER

OLD es la variable que almacena la información antigua relacionada con la consulta que ejecuta el TRIGGER, esta información la componen las filas que van a ser borradas o modificadas. En un DELETE por ejemplo, OLD tendrá la información de todas las columnas de los registros borrados.

Al igual que NEW no está disponible para todos los TRIGGER que creemos, la variable OLD no podrá ser utilizada para un INSERT, ya que en este tan solo existe nueva información que va a ser insertada en la base de datos.

Ejemplo de un TRIGGER sencillo

Vamos a resolver un TRIGGER que nos permita mantener una copia de todos los clientes que se inserten en una base de datos de una tienda online. Para esto tendremos dos tablas: cliente y cliente_historico. Así, el siguiente TRIGGER insertará toda la información del cliente (nombre, dni, direccion) más una columna extra: fecha_registro.

DELIMITER $$
CREATE TRIGGER trigger_cliente_historico 
AFTER INSERT ON cliente
FOR EACH ROW
BEGIN 
   INSERT INTO cliente_historico(nombre, dni, direccion)
   VALUES (NEW.nombre, NEW.dni, NEW.direccion, CURDATE());
END; $$

Ejemplo 2 de un TRIGGER sencillo

El siguiente TRIGGER insertará un valor nuevo en un campo único identificador tras una actualización de datos en la tabla usuario_web:

DELIMITER $$
CREATE TRIGGER trigger_usuario_identificador
AFTER UPDATE ON usuario
FOR EACH ROW
BEGIN 
   UPDATE usuario SET identificador = CONCAT(NEW.id, '_', NEW.nombre, '_', NEW.dni)
   WHERE id = OLD.id;
END; $$

Resumen y consejos para utilizar TRIGGER:

En resumen un TRIGGER sirve para:

• Ejecutar un código SQL cuando ocurra un evento en concreto: INSERT, UPDATE o DELETE
• Ayudar a mantener la integridad de la información
• Manipular la información de una consulta en concreto ANTES o DESPUES de su ejecución

La estructura de un disparador en orden es la siguiente:

1. Definir un símbolo o conjunto de ellos que serán el delimitador para nuestra programación. Para esto usaremos la sentencia DELIMITER.
2. Utilizar el comando CREATE de SQL para crear el disparador y asociarle un nombre: CREATE TRIGGER nombre_disparador
3. Indicar un evento sobre una tabla que producirá la ejecución del disparador: AFTER/BEFORE DELETE/UPDATE/INSERT ON tabla
4. Escribir FOR EACH ROW para que se ejecute para cada registro insertado/actualizado/borrado.
5. Delimitar la zona de programación mediante BEGIN y END.
6. Por último cerrar la declaración del TRIGGER mediante el uso del símbolo o conjunto de símbolos que indicamos al principio.

Cuando utilizamos TRIGGER's en nuestras bases de datos debemos tener en cuenta varias cosas:

• Debemos tener mucho cuidado con que un TRIGGER no ejecute a otro y este a su vez al anterior. Sería un bucle infinito de ejecución.
• Se puede tener varios disparadores que se ejecuten en el mismo evento y momento sobre una misma tabla. La idea es separar código en pequeños fragmentos para evitar un mantenimiento engorroso.
• Si vas a sobrescribir un TRIGGER utiliza el comando DROP TRIGGER IF EXISTS nombre_trigger; en la siguiente línea al delimitador DELIMITER.
• OLD no es accesible en los registros de un INSERT.
• NEW no está disponible en los registros de un DELETE.

Extra: Creación y uso de variables en un TRIGGER

Es importante saber que al igual que en funciones y procedimientos almacenados, en un TRIGGER podemos crear variables y manipularlas. La creación de una variable se realiza mediante el comando DECLARE seguido del nombre de la variable y su tipo. Por ejemplo:

DECLARE $var1 SMALLINT;

Y la asignación o modificación de su valor mediante el comando SET:

SET $var1 = 33;

Como verás he nombrado a las variables con un dólar al principio, esto no es más que una forma mía de diferenciar mi variable de los nombres de columnas o tablas que use en el TRIGGER. Puedes crear las variables sin dólar o incluso poner otro carácter en su lugar.

Para mayor claridad te dejo el último TRIGGER de ejemplo modificado con uso de variables:

DELIMITER $$
CREATE TRIGGER trigger_usuario_identificador
AFTER UPDATE ON usuario
FOR EACH ROW
BEGIN 
   DECLARE $identificador TEXT;
   SET $identificador = CONCAT(NEW.id, '_', NEW.nombre, '_', NEW.dni);
   UPDATE usuario
   SET identificador = $identificador 
   WHERE id = OLD.id;
END; $$