Taller de base de datos Triggers
6.2 DISPARADORES (Triggers)
A partir de MySQL 5.0.2 se incorpor el soporte bsico para disparadores (triggers). Un disparador es un objeto con nombre dentro de una base de datos el cual se asocia con una tabla y se activa cuando ocurre en sta un evento en particular. Por ejemplo, las siguientes sentencias crean una tabla y un disparador para sentencias INSERT dentro de la tabla. El disparador suma los valores insertados en una de las columnas de la tabla. Un disparador se asocia con una tabla y se define para que se active al ocurrir una sentencia INSERT, DELETE, o UPDATE sobre dicha tabla. Puede tambin establecerse que se active antes o despus de la sentencia en cuestin. Por ejemplo, se puede tener un disparador que se active antes de que un registro sea borrado, o despus de que sea actualizado. Un Disparador nunca se llama directamente, en cambio, cuando una aplicacin o usuario intenta insertar, actualizar, o anular una fila en una tabla, la accin definida en el disparador se ejecuta automticamente (se dispara). Un trigger no puede llevar el comando Commit o Rollback (ni los bloques que dicho trigger invoque). Los triggers tienen dos palabras clave, OLD y NEW que se refieren a los valores que tienen las columnas antes y despus de la modificacin. Los INSERT permiten NEW, los DELETE slo OLD y los UPDATE ambas.
RDENES DE DISPARADORES
Eliminacin de un disparador DROP TRIGGER nombre; Habilitacin/deshabilitacin de un disparador ALTER TRIGGER nombre [ENABLE|DISABLE] Habilitacin/deshabilitacin de todos los disparadores de una tabla: ALTER TABLE nombretabla [ENABLE|DISABLE] ALL TRIGGERS; Ver todos los disparadores definidos por un usuario SELECT TRIGGER_NAME FROM USER_TRIGGERS Ver el cuerpo de un disparador: SELECT TRIGGER_BODY FROM USER_TRIGGERSWHERE TRIGGER_NAME = nombre_disparador; Ver la descripcin de un disparador: SELECT DESCRIPTION FROM USER_TRIGGERSWHERE TRIGGER_NAME = nombre_disparador
Taller de base de datos Triggers
Sintaxis necesaria para crear un trigger: CREATE TRIGGER <nombre> {BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON <nom_tabla> FOR EACH ROW BEGIN <sentencias_SQL> END; BEFORE|AFTER: Especifican cuando se dispara el trigger (antes de ejecutar la sentencia o despus) INSERT|UPDATE|DELETE: Especifican el evento que disparar el trigger ON nom_tabla: Indica la tabla sobre la que acta el trigger Sentencias_SQL: Son las sentencias que se ejecutarn cuando se active el trigger Ejercicios de Triggers en MySQL 1.- Entrar a la consola de Phpmyadmin o en el modo grafico del mismo. 2.- Creamos la bases de datos prueba_triggers. 3.- Ponemos en uso la base de Datos mysql> use prueba; 4.- Creamos la tabla clientes: mysql> CREATE TABLE Clientes(id_Cliente int not null auto_increment, Nombre varchar(100), Seccion varchar(20), Accion varchar(10) default Insertado, PRIMARY KEY(id_Cliente), Key(Nombre) ) ENGINE = InnoDB; 4.- Ahora crearemos la tabla auditoria_clientes, que ser utilizada para registrar todos los cambios, que se hagan en la tabla clientes: mysql> CREATE TABLE auditoria_clientes(id int not null auto_increment, Nom_anterior varchar(100), Secc_anterior varchar(20), Nom_nuevo varchar(100), Secc_nueva varchar(20), usuario varchar(40), modificado datetime, proceso varchar(10), Id_Cliente int(4), primary key(id) ) ENGINE = InnoDB; 5.- Comenzamos a crear los tres disparadores o trigger.
Taller de base de datos Triggers
Creamos Trigger para inserter CREATE TRIGGER Inserta_auditoria_clientes AFTER INSERT ON Clientes FOR EACH ROW INSERT INTO auditoria_clientes(Nom_nuevo, Secc_nueva, usuario, modificado, proceso, Id_Cliente) VALUES ( NEW.Nombre, NEW.Seccion, CURRENT_USER(), NOW(), NEW.Accion, NEW.id_Cliente); Creamos Trigger: para modificar CREATE TRIGGER Modifica_auditoria_clientes BEFORE UPDATE ON Clientes FOR EACH ROW INSERT INTO auditoria_clientes(Nom_anterior, Secc_anterior, Nom_nuevo, seccion_nueva, usuario, modificado, proceso, Id_Cliente) VALUES (OLD.Nombre, OLD.Seccion, NEW.Nombre, NEW.Seccion, CURRENT_USER(), NOW(), Modificado, NEW.id_Cliente); Creamos Trigger : para eliminar CREATE TRIGGER Elimina_auditoria_clientes AFTER DELETE ON clientes FOR EACH ROW INSERT INTO auditoria_clientes(Nom_anterior, Secc_anterior, usuario, modificado, proceso, Id_Cliente) VALUES (OLD.Nombre, OLD.Seccion, CURRENT_USER(), NOW(), Eliminado, OLD.id_Cliente); 6.- Se inserta Datos a la tabla de clientes: INSERT INTO clientes (id_Cliente, Nombre, Seccion) VALUES('Miguel','informtica'),( 'Rosa','comida'),( 'Mara','ropa'), ('Alberto','informtica'),('Jordi','comida');
SELECT * FROM `Auditoria_Clientes`
Id_Cliente Nombre Seccion Accion 1 Miguel informtica Insertado 2 3 4 5 Rosa Mara comida ropa Insertado Insertado
Alberto informtica Insertado Jordi comida Insertado
SELECT * FROM `clientes`
Taller de base de datos Triggers
i d 1 2 3 4 5
Nom_Anteri or NULL NULL NULL NULL NULL
Secc_Anteri or NULL NULL NULL NULL NULL
Nom_nuev o Miguel Rosa Mario Alberto Jordi
Secc_nuev a informtica comida ropa informtica comida
Usuario
Proces o
Modificad o
2013-11-04 19:51:17 2013-11-04 19:51:17 2013-11-04 19:51:17 2013-11-04 19:51:17 2013-11-04 19:51:17
id_Client e 1 2 3 4 5
root@localho st root@localho st root@localho st root@localho st root@localho st
Insertad o Insertad o Insertad o Insertad o Insertad o
Otro Ejemplo: Creamos 3 disparadores sobre la tabla de usuarios, estos nos dirn las acciones que se realicen sobre esta (insercin, modificacin o borrado).La estructura de las tablas sern las siguientes: /* Creamos la tabla de acciones*/ CREATE TABLE acciones( fecha DATE, hora TIME, Accin VARCHAR(50), idusuario int); /*Creamos la tabla de usuarios*/ CREATE TABLE usuarios( idusuario integer PRIMARY KEY, nombre VARCHAR(50), apellido VARCHAR(50)); Las sentencias para lo registro de inserciones*/ DELIMITER | CREATE TRIGGER inser_usuarios BEFORE INSERT ON usuarios FOR EACH ROW BEGIN INSERT INTO acciones VALUES(CURDATE(),CURTIME(), 'Registro insertado', new.idusuario); END; | DELIMITER ; /*Disparador para el registro modificado*/ DELIMITER | CREATE TRIGGER update_usuarios BEFORE UPDATE ON usuarios FOR EACH ROW BEGIN INSERT INTO acciones VALUES(CURDATE(), CURTIME(), 'Registro Modificado', old.idusuario);
Taller de base de datos Triggers
END; | DELIMITER ; /*Disparador para el registro eliminado*/ DELIMITER | CREATE TRIGGER delete_usuarios BEFORE DELETE ON usuarios FOR EACH ROW BEGIN INSERT INTO acciones VALUES(CURDATE(), CURTIME(), 'Registro eliminado', old.idusuario); END; | DELIMITER ; NOTA: La palabra DELIMITER sirve para iniciar y terminar un
disparador (Triggers), estn formados por varias instrucciones, por lo tanto, antes de comenzar su definicin, debes especificar qu carcter delimita (o separa). Ahora Insertar un Registro y ver que sucede
