Triggers o disparadores en MySQL

Antes de ver el siguiente articulo me gustaria que tuvieran en cuenta la Relacion Costo Beneficio que esto trae, si bien recordemos que algunas empresas no activan el modo auditoria porque este consume casi el total de recursos o simplemente no tienen personas en el area de sistemas que se encargen de revisar estos logs.

----------------------------------------------------------------------------------------------------------------

Un trigger o disparador es un objeto de base de datos que se asocia a una tabla y se activa cuando se produce algún evento sobre dicha tabla.

Supongamos que tenemos una tabla, llamada empleados, formada por varios campos, y queremos registrar en otra tabla, llamada auditoría, las modificaciones que se produzcan sobre la tabla empleados.

Por ejemplo, podríamos registrar en la tabla auditoría la fecha y hora en la que se realiza algún cambio en la tabla empleados, además del valor modificado.

Todo esto es posible gracias a los triggers, que como he dicho antes, se activan cuando se produce algún evento sobre alguna tabla.

¿Cómo crear un trigger en MySQL?

La sintaxis básica para crear un trigger en MySQL es la siguiente:

CREATE TRIGGER nombre_trigger
{BEFORE|AFTER} {INSERT|UPDATE|DELETE}
ON nombre_tabla
FOR EACH ROW BEGIN
sentencias_sql;
END;

• BEFORE|AFTER espefican cuando se dispara el trigger (antes de ejecutar la sentencia o despúes)
• INSERT|UPDATE|DELETE especifican el evento que disparará el trigger
• ON nombre_tabla indica la tabla sobre la que actúa el trigger
• sentencias_sql son las sentencias que se ejecutarán cuando se active el trigger

Veamos un ejemplo.

Vamos a construir un trigger que se activará cada vez que insertemos un registro en una tabla llamada empleados. Lo que hará el trigger será insertar en una tabla, llamada AUDITORIA, la fecha y hora en la que se ha realizado la inserción del registro en la tabla empleados.

Así pues, nuestro primer paso será crear la tabla AUDITORIA de la siguiente forma:

CREATE TABLE auditoria(
fecha DATE,
hora TIME);

Esta tabla estará formada por dos campos que llamaremos fecha y hora.

A continuación creamos la tabla empleados, formada por los campos emp_no, apellidos y salario.

CREATE TABLE empleados(
emp_no integer PRIMARY KEY,
apellidos VARCHAR(50),
salario INTEGER);

Y acto seguido crearemos el trigger, que llamaremos trigger1, de la siguiente forma:

DELIMITER |
CREATE TRIGGER trigger1 BEFORE INSERT ON empleados
FOR EACH ROW BEGIN
INSERT INTO auditoria VALUES(CURDATE(),CURTIME());
END;
| DELIMITER ;

La palabra DELIMITER se utiliza para indicar cuál será el carácter utilizado para delimitar la última línea del trigger. En este caso se utiliza el carácter barra (|) justo antes de la sentencia de creación del trigger (CREATE TRIGGER ….) y al final de la última línea de código del trigger. Cuando creamos el trigger, escribimos de nuevo DELIMITER seguido de un punto y coma para indicar que el final de la orden SQL será el punto y coma a partir de ahora.

CURDATE() y CURTIME() son dos funciones internas de MySQL que devuelven la fecha y la hora actual. Así pues, lo que hará el trigger será insertar la fecha y hora actual, en la tabla auditoría, cada vez que se inserte un registro en la tabla empleados.

Para probar el trigger introduciremos un registro en la tabla empleados.

• INSERT INTO EMPLE VALUES(1,’Serrano Pérez’,1500);

Y a continuación consultaremos la tabla auditoría para observar que efectivamente queda registrada la fecha y hora.

Veamos otro ejemplo. Vamos a crear una tabla llamada test1 con tan sólo un campo, y vamos a asociar un trigger a esta tabla que registrará en otra tabla, llamada auditoría, la fecha y hora en la que se actualiza la tabla test1 y los valores nuevos y antiguos del campo de la tabla test1.

Así pues, creamos en primer lugar la tabla test1.

CREATE TABLE test1(
campo1 VARCHAR(10));

A continuación creamos la tabla AUDITORIA2.

CREATE TABLE auditoria2(
antiguovalor varchar(10),
nuevovalor varchar(10),
fecha date,
hora time);

Y este es el trigger que asociamos a la tabla test1.

DELIMITER |
CREATE TRIGGER trigger2 AFTER UPDATE ON test1
FOR EACH ROW BEGIN
INSERT INTO test1 VALUES(OLD.campo1,NEW.campo2,CURDATE(),CURTIME());
END;
| DELIMITER ;

Observa que en este caso hacemos uso de AFTER UPDATE, para indicar al trigger que se debe activar cuando se produzca alguna modificación en la tabla test1.

En la sentencia INSERT aparecen los valores OLD y NEW. NEW.campo1 es el nuevo valor que se da a la columna campo1, OLD.campo1 es el antiguo valor que tenía el campo antes de la actualización.

Por último vamos a crear un trigger que se activará cuando se elimine algún registro de la tabla test1.

DELIMITER |
CREATE TRIGGER trigger3 AFTER DELETE ON test1
FOR EACH ROW BEGIN
INSERT INTO auditoria3 VALUES(OLD.campo1,CURDATE(),CURTIME());
| DELIMITER ;

En este caso el trigger insertaría un registro en la tabla auditoria3 cada vez que se eliminara algún registro de la tabla test1. Observa como en este caso no tiene sentido utilizar NEW.campo1, ya que al borrar el registro lo único que tiene sentido es el antiguo valor que tenía el campo, OLD.campo1.

Fuente y Agradecimiento:

http://www.alejandrox.com/2008/04/triggers-o-disparadores-en-mysql/