Triggers o disparadores en MySQL

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.

Popularity: 5% [?]

Otras entradas que te pueden interesar

Esta entrada tiene

13 comentarios

Escrito por Alejandro Meroño Hernández

Abril 8th, 2008 at 5:19 pm

Categoría: Bases de datos

13 comentarios en 'Triggers o disparadores en MySQL'

Suscríbete a comentarios con RSS o TrackBack en 'Triggers o disparadores en MySQL'.

Buenas, realmente las explicaciones son muy claras. Solo veo un error en la creación del trigger2 dónde pones “OLD.campo1,NEW.campo2″ debería ser “OLD.campo1,NEW.campo1″.
Saludos y sigue asi.

Xavier

6 Jun 08 at 8:47 am
ok ke buena explicaion, siempre quise entender algo acerca de los trigger graxxxxxx

xxx fin

naranjo

2 Jul 08 at 3:20 pm
bueno,otro error en trigger2
en ves de:
INSERT INTO test1 //en la linea 4
deberia ser:
INSERT INTO auditoria2

ademas del error antes mencionado:
“OLD.campo1,NEW.campo2″
debería ser
“OLD.campo1,NEW.campo1″.

bueno ,otro error

6 Jul 08 at 6:28 pm
kiero hacer un trigger after delete en el q, al borral los datos de mi tabla animal o encargado, tambien se borren de la tabal protegidos (tabla no enlazada a otra)

Paula Reynel

24 Oct 08 at 4:15 am
lo que le falta al trigers 3 para que funcione es esto END;
por lo que seria asi
DELIMITER $$

DROP TRIGGER /*!50032 IF EXISTS */ `hospital`.`jh`$$

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

DELIMITER ;

guerra olivares ronald

12 Oct 09 at 4:48 am
hola necesito saber como hacer un trigger para que al insertar el valor de un campo cumpla con la condicion que este entre los valores 100 y 200, gracias

noe

5 Nov 09 at 7:00 pm
HOLA OCUPO SABER COMO HACER UN TRIGGER PARA UNA BASE DE DATOS QUE NO SE PUEDA ACTUALIZAR Y QUE MANDE UN MENSAJE QUE DIGA LO MISMO Y USANDO DELIMITER COMO LE HAGO URGE PORFAVOR SI ES ANTES DE LAS 10 AM

MARCOS

20 Nov 09 at 8:46 am
hola soy estudiante de informatica de la universidad del pacifico y mi comentario es que se especifique un poco mas

sandra gisela diaz hurtado

20 Nov 09 at 7:08 pm
Que version de MYSQL es necesaria para este ejemplo?

Sonia

13 Ene 10 at 9:13 am
muy bien gracias

Anónimo

5 Mar 10 at 6:03 am
Hola tengo un problema con un trigger en mysql, resulta que estoy usando MySQL Worckwrench 5.2 y con el he creado 2 tablas en mysql una de clientes y otra de citas, el punto es que trato con un trigger de tomar el último ID (autoincrementado), que se crea tras la inserción en la tabla clientes por un store que cree he insertar ese ID en lo que sería la llave foranea en la tabla de citas, hasta el momento lo que he hecho es lo siguiente:

– Trigger DDL Statements
USE `Masaje`;
DELIMITER $$

CREATE Trigger Tr_inserta_idCliente after insert on Masaje.Clientes
for each row
begin
Set uval = mysql_insert_id();
insert into masaje.citas(FechaCita,NumCita,DestalleCita,Clientes_idClientes)
values(’0000-00-00′,0,’nada’,uval);
end /*(select LAST_INSERT_ID())*/
$$

ya he probado haciendo un declare de la variable uval, o nadamás seteando esa variable como está en el código, y hasta usando la función Last_insert_id(); y el resultado cuando despliego la información en el Query es: que si se inserta el cliente en la tabla Clientes pero el trigger no me inserta en la tabla citas el Id del cliente hojalá puedas ayudarme gracias por cierto este tema que has escrito esta muy bien, gracias espero vuestra ayuda Saludos.

tocallo

13 Abr 10 at 12:17 am
alguien me puede decir que tiene de mal este trigger , me da error antes del else, ya probe con ; paretecis de todo , si alguno me puede dar una mano , gracias.
CREATE TRIGGER it_retiro_x_vendedor BEFORE INSERT ON retiro_x_vendedor
FOR EACH ROW
IF EXISTS (SELECT 1 FROM stock_vendedor
WHERE NEW.cod_vendedor=stock_vendedor.cod_vendedor
AND NEW.cod_producto=Stock_vendedor.cod_producto)
UPDATE stock_vendedor
SET cant_vendedor = cant_vendedor+NEW.cant_retiro
WHERE NEW.cod_vendedor=stock_vendedor.cod_vendedor
AND NEW.cod_producto=Stock_vendedor.cod_producto
ELSE
INSERT INTO stock_vendedor VALUES (NEW.cod_vendedor,NEW.cod_producto,NEW.cant_retiro);
END IF

Lucas

20 Abr 10 at 2:08 am
perdon me falto el then

CREATE TRIGGER it_retiro_x_vendedor BEFORE INSERT ON retiro_x_vendedor
FOR EACH ROW
IF EXISTS SELECT 1 FROM stock_vendedor
WHERE NEW.cod_vendedor=stock_vendedor.cod_vendedor
AND NEW.cod_producto=Stock_vendedor.cod_producto
THEN
UPDATE stock_vendedor
SET cant_vendedor = cant_vendedor+NEW.cant_retiro
WHERE NEW.cod_vendedor=stock_vendedor.cod_vendedor
AND NEW.cod_producto=Stock_vendedor.cod_producto
ELSE
INSERT INTO stock_vendedor VALUES (NEW.cod_vendedor,NEW.cod_producto,NEW.cant_retiro);
END IF

Lucas

20 Abr 10 at 2:21 am