Triggers, disparadores en MySQL

Hace unos años necesitaba llevar un registro de las operaciones que hacían los usuarios en determinado sistema, lo que se me ocurrió en aquel momento, fue actualizar desde PHP una tabla LOG en la base de datos y así llevar ese seguimiento; fue un poco tedioso y desconocía los triggers (disparadores).

Un trigger es un procedimiento que se ejecuta cuando se cumple una condición establecida al realizar una operación. Dependiendo de la base de datos, los triggers pueden ser de inserción (INSERT), actualización (UPDATE) o borrado (DELETE). Es decir, son objetos que se asocian a una tabla y se activan cuando ocurre un determinado evento en dicha tabla, por ejemplo, cuando agregamos un nuevo registro.

Los disparadores son soportados en MySQL a partir de la versión 5.0.2 y su sintaxis es muy sencilla:

CREATE TRIGGER [nombre_del_trigger]
[momento] [evento]
ON [nombre_tabla] FOR EACH ROW
BEGIN 
   [proceso]
END;

Donde:

[nombre_del_trigger]  --> nombre del disparador, debe ser único.
[momento] --> determina cuando se ejecutara el disparador, puede ser BEFORE (antes) o AFTER (después) del evento.
[evento] --> determina que proceso (setencia) llamara al disparador, puede ser INSERT (insertar), UPDATE (actualizar) o DELETE (eliminar) datos.
[nombre_tabla] --> será la tabla a la que se asigne el disparador (trigger).
[proceso] --> determina las instrucciones a seguir, bien prodria ser una sola sentencia y no haría falta en ese caso usar la sentencia compuesta BEGIN ..END, de lo contrario, es necesario.

En el caso de usar múltiples sentencias dentro del disparador, al igual que como vimos en los stored procedure, será preciso usar DELIMITER para cambiar el carácter delimitador dentro de MySQL.

Las sentencia siguiente, FOR EACH ROW:

.. define lo que se ejecutará cada vez que el disparador se active, lo cual ocurre una vez por cada fila afectada por la sentencia activadora.

Ahora mostrare un ejemplo practico del uso de los triggers, dispongo de una tabla donde almaceno datos de artículos y deseo que se lleve un control de las operaciones que en ella se realizan.

El siguiente trigger se ejecuta después de la creación de un nuevo registro:

CREATE TRIGGER trigger_log_articulos 
AFTER 
   INSERT ON articulos
FOR EACH ROW
   INSERT INTO log_articulos(fecha,usuario,proceso,articulo)
   VALUES (NOW(),CURRENT_USER(),'1',NEW.articulo);

Almacenará la fecha de la inserción, el nombre del usuario, el tipo de proceso y el id del registro agregado.

veamos un ejemplo practico:

(clic para mostrar/ocultar el código)

mysql> -- creamos la base de datos
mysql> CREATE DATABASE triggers_db;
Query OK, 1 row affected (0.00 sec)

mysql> -- seleccionamos la db
mysql> USE triggers_db;
Database changed

mysql> -- tabla donde se almacenan los artículos
mysql> CREATE TABLE articulos(
    ->    id_articulo int not null auto_increment primary key,
    ->    titulo varchar(200) not null,
    ->    contenido Blob not null, -- máximo de 65.535 caracteres
    ->    autor varchar(25) not null, -- podría ser el id de una tabla "usuarios"
    ->    fecha_pub datetime not null, -- fecha de la publicacion
    ->    estado bool not null default 1 -- 1 ó 0
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.15 sec)

mysql> -- tabla que almacena los proceso realizados sobre la tabla "articulos"
mysql> CREATE TABLE log_articulos
    -> (
    ->    id_log_art int not null auto_increment primary key,
    ->    fecha datetime, -- fecha del proceso
    ->    usuario varchar(40), -- usuario implicado
    ->    proceso varchar(10), -- agregado, editado, eliminado
    ->    articulo varchar(200) not null -- titulo del articulo  
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.13 sec)

mysql> -- creamos el disparador
mysql> CREATE TRIGGER tgr_ins_logarticulos
    -> AFTER 
    ->    INSERT ON articulos
    -> FOR EACH ROW
    ->    INSERT INTO log_articulos(fecha,usuario,proceso,articulo)
    ->       VALUES (NOW(),CURRENT_USER(),'agregado',NEW.titulo);
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO articulos (titulo,contenido,autor,fecha_pub) VALUES 
    -> ('ejemplo de triggers en MySQL','contenido..','autor_x',NOW());
Query OK, 1 row affected (0.07 sec)

mysql> select * from log_articulos;
+------------+---------------------+----------------+----------+------------------------------+
| id_log_art | fecha               | usuario        | proceso  | articulo                     |
+------------+---------------------+----------------+----------+------------------------------+
|          1 | 2011-07-25 17:20:14 | root@localhost | agregado | ejemplo de triggers en MySQL |
+------------+---------------------+----------------+----------+------------------------------+
1 row in set (0.00 sec)

En el ejemplo se ve, que una vez se inserta un registro en la tabla articulos, el trigger se ejecuta y almacena datos de la operación en la tabla log_articulos.

Si en una misma sentencia insertamos más de un registro, se dispararan tantos procesos como registros haya.

---

- OLD y NEW
permiten acceder a valores de las columnas de los registros afectados por un disparador.

INSERT --> solamente trabaja con NEW.nombre_columna (nuevos datos insertados).
DELETE --> solamente trabaja con OLD.nombre_columna (datos eliminados).
UPDATE --> se puede emplear OLD.nombre_columna (datos de la columna antes de actualizar)  y NEW.nombre_columna (datos de la columna luego de actualizar).

Cabe acotar, que para poder ejecutar cualquier disparador se necesitan los privilegios correspondientes.

Para mostrar los triggers dentro de una base de datos, usamos:

show triggers;

Podemos eliminar un disparador si posee un nombre que vayamos a usar:

DROP TRIGGER IF EXISTS trigger_ejemplo

De esa manera se eliminaría el disparador de nombre trigger_ejemplo.

---

Si intentas crear un trigger sobre dos o más eventos simultáneamente, no podrás por errores de sintaxis; MySQL no permite definir más de un evento para el mismo trigger. Eso implica que si quieres controlar tres eventos sobre una misma tabla (insert, update, delete) tendrás que usar tres disparadores diferentes.

A continuación otro ejemplo, pero aplicado a UPDATE Y DELETE.

(clic para mostrar/ocultar el código)

-- creamos el disparador
CREATE TRIGGER tgr_upd_logarticulos 
BEFORE 
   UPDATE ON articulos
FOR EACH ROW
   INSERT INTO log_articulos(fecha,usuario,proceso,articulo)
      VALUES (NOW(),CURRENT_USER(),'editado',NEW.articulo);

-- creamos el disparador
CREATE TRIGGER tgr_del_logarticulos 
AFTER
   DELETE ON articulos
FOR EACH ROW
   INSERT INTO log_articulos(fecha,usuario,proceso,articulo)
      VALUES (NOW(),CURRENT_USER(),'eliminado',OLD.articulo);

Una consulta a la tabla log_articulos, luego de insertar, editar y eliminar registros en la tabla artículos:

SELECT CONCAT(articulo,' -> ',proceso,' por ',usuario,' el ',fecha) AS log_articulos 
FROM log_articulos;
+-------------------------------------------------------------------------------------+
| log_articulos                                                                       |
+-------------------------------------------------------------------------------------+
| Triggers en MySQL -> agregado por root@localhost el 2011-07-25 17:20:14             |
| Ejemplo de triggers en MySQL -> agregado por root@localhost el 2011-07-25 17:20:25  |
| Stored procedures en MySQL -> agregado por root@localhost el 2011-07-25 17:26:48    |
| Tips en PHP -> agregado por root@localhost el 2011-07-25 17:26:48                   |
| Repasando el XML -> agregado por root@localhost el 2011-07-25 17:26:48              |
| Transiciones con CSS -> agregado por root@localhost el 2011-07-25 17:26:48          |
| Tips en PHP -> editado por root@localhost el 2011-07-25 17:29:06                    |
| Ejemplo de triggers en MySQL -> eliminado por root@localhost el 2011-07-25 17:32:33 |
| Triggers en MySQL -> editado por root@localhost el 2011-07-25 17:33:25              |
+-------------------------------------------------------------------------------------+

Referencias: 
Triggers en MySQL (Documentación oficial)