Tiggers Triggers en SQL
Presenter Notes
Disparadores :
Un trigger (o disparador) en una Base de datos, es un procedimiento que se ejecuta cuando se cumple una condición establecida al realizar una operación. en MySQL los triggers pueden procesar eventos en determinados momentos:
Presenter Notes
Eventos y momentos
A continuación mostramos la lista de eventos que podemos disparar en determinado momento:
Eventos disponibles en Triggers:
- Inserción (
INSERT) - Actualización (
UPDATE) - Borrado (
DELETE)
A su vez podemos procesar estos eventos en dos momentos:
Momentos disponibles para los Eventos:
- Antes
BEFORE - Después
AFTER
En otras palabras podemos atrapar 3 eventos en 2 momentos por cada tabla en nuestra base de datos.
Presenter Notes
Sintaxis Básica:
1 CREATE TRIGGER nombre_disp
2 momento_disp evento_disp ON nombre_tabla
3 FOR EACH ROW sentencia_disp;
El disparador queda asociado a la tabla nombre_tabla. Esta debe ser una tabla permanente, no puede ser una tabla TEMPORARY ni una vista.
momento_disp es el momento en que el disparador entra en acción. Puede ser BEFORE (antes) o AFTER (después), para indicar que el disparador se ejecute antes o después que la sentencia que lo activa.
sentencia_disp es la sentencia que se ejecuta cuando se activa el disparador.
Borrar un disparador
1 DROP TRIGGER nombre_disp;
La sentencia DROP TRIGGER necesita que se posea el privilegio SUPER.
Viendo las disparadores existentes:
1 SHOW TRIGGERS;
Presenter Notes
Restricciones En Disparadores:
Si se desean ejecutar múltiples sentencias, deben colocarse entre BEGIN ... END, el constructor de sentencias compuestas.
Para esto es necesario modifcar el delimitador de fin de instrucción (DELIMITER) y al final restaurar por el delimitador por defecto ;.
Ejemplo disparador con multiples instrucciones:
1 DELIMITER |
2 CREATE TRIGGER testref BEFORE INSERT ON test1
3 FOR EACH ROW BEGIN
4 INSERT INTO test2 SET a2 = NEW.a1;
5 DELETE FROM test3 WHERE a3 = NEW.a1;
6 UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
7 END
8 |
9 DELIMITER ;
No puede haber dos disparadores en una misma tabla que correspondan al mismo momento y sentencia. Por ejemplo, no se pueden tener dos disparadores BEFORE UPDATE. Pero sí es posible tener los disparadores BEFORE UPDATE y BEFORE INSERT o BEFORE UPDATE y AFTER UPDATE.
Presenter Notes
Ejemplo completo:
Vamos a crear una base de datos con el nombre prueba_triggers, y nos seleccionamos para usar dicha base de datos, como se muestra a continuación:
1 CREATE DATABASE prueba_triggers;
2 USE prueba_triggers;
Presenter Notes
Creando tablas:
Posteriormente creamos dos tablas: alumnos y logs, donde en alumnos vamos a agregar un conjunto de disparadores que van a guardar registros en la tabla logs:
1 -- Borramos(si existe) y Creamos tabla alumnos
2 DROP TABLE IF EXISTS alumnos;
3 CREATE TABLE alumnos(
4 id INT(3)
5 PRIMARY KEY NOT
6 NULL AUTO_INCREMENT,
7 equipo INT(2),
8 nombre VARCHAR(150),
9 fecha_registro DATETIME
10 );
11
12 -- Borramos(si existe) y creamos la tabla logs para guardar
13 -- los eventos de la tabla
14 DROP TABLE IF EXISTS logs;
15 CREATE TABLE logs(
16 id INT(5)
17 PRIMARY KEY NOT NULL
18 AUTO_INCREMENT,
19 msg VARCHAR(200)
20 );
Presenter Notes
Disparador BEFORE INSERT
Ejemplo agregando un disparados sobre la tabla alumnos
Vamos a crear un disparador que se va a encargar en generar la fecha de registro al agregar(insert) un nuevo alumno, ademas agregamos un registro en la tabla logs indicando que se va a guardar un determinado alumno:
1 -- Borramos el disparador si es que existe
2 DROP TRIGGER IF EXISTS auto_gen_fecha_reg;
3 -- Cambiamos el delimitador
4 delimiter //
5 CREATE TRIGGER auto_gen_fecha_reg
6 BEFORE INSERT ON alumnos
7 FOR EACH ROW
8 BEGIN
9 SET NEW.fecha_registro = (SELECT NOW());
10 SET NEW.equipo = (SELECT ROUND(RAND()*3));
11 INSERT INTO logs VALUES(
12 NULL,
13 CONCAT('Previo a guardar alumno: ', NEW.nombre)
14 );
15 END;//
16 delimiter ;
Presenter Notes
Disparador AFTER INSERT
Vamos a crear un disparador que se va a encargar agregar un registro en la tabla logs indicando que se guardo un determinado alumno:
1 -- Borramos el disparador si es que existe
2 DROP TRIGGER IF EXISTS despues_de_add_user;
3 -- Cambiamos el delimitador
4 delimiter //
5 CREATE TRIGGER despues_de_add_user
6 AFTER INSERT ON alumnos
7 FOR EACH ROW
8 BEGIN
9 INSERT INTO logs VALUES(
10 NULL,
11 CONCAT('Despues de guardar alumno: ', NEW.nombre)
12 );
13 END;//
14 delimiter ;
Presenter Notes
Disparador AFTER UPDATE
De forma análoga podemos agregar los disparadores after update y el after delete, como se muestra a continuación:
1 -- Borramos el disparador si es que existe
2 DROP TRIGGER IF EXISTS despues_de_edit_user;
3
4 -- Cambiamos el delimitador
5 delimiter //
6 CREATE TRIGGER despues_de_edit_user
7 AFTER UPDATE ON alumnos
8 FOR EACH ROW BEGIN
9 INSERT INTO logs VALUES(
10 NULL,
11 CONCAT(
12 'Se modifico el usuario: ', OLD.nombre
13 ,' Su nuevo nombre es:', NEW.nombre
14 )
15 );
16 END;//
17 delimiter ;
Presenter Notes
Disparador AFTER DELETE
1 -- Borramos el disparador si es que existe
2 DROP TRIGGER IF EXISTS borrando_alumno;
3
4 -- Cambiamos el delimitador
5 delimiter //
6 CREATE TRIGGER borrando_alumno
7 AFTER DELETE ON alumnos
8 FOR EACH ROW BEGIN
9 INSERT INTO logs VALUES(
10 NULL,
11 CONCAT('Se a borrado el alumno: ', OLD.nombre)
12 );
13 END;//
14 delimiter ;
A continuación veremos a ver algunas otras cosas que podemos hacer con los disparadores.
Presenter Notes
Uso de señales en disparadores:
Una señal (SIGNAL) es una forma de regresar un "error" al motor de MySQL, este error lo podemos manejar por medio de:
- Un numero de error
- Un estado SQL(SQLSTATE)
- Y/o un mensaje.
Usted puede ocupar el SQLSTATE con el valor '45000' para generar un error genérico, esto resulta muy útil en el momento de querer alterar el comportamiento del motor de MySQL como lo vamos a ver a continuación:
Nota: El lector puede abundar mas en el tema: http://dev.mysql.com/doc/refman/5.5/en/signal.html
Presenter Notes
Ejemplo disparador de validación:
Vamos a agregar un disparador que al modificar un alumno valide que el campo nombre no sea menor a 6 caracteres, en caso contrario vamos a generar un error genérico (SQLSTATE 45000) y vamos a generar un mensaje para informar al cliente o a nuestra aplicación el motivo del error:
1 CREATE TRIGGER antes_de_edit_user
2 AFTER UPDATE ON alumnos
3 FOR EACH ROW
4 BEGIN
5 SET @tam_nombre = LENGTH(NEW.nombre);
6 IF @tam_nombre < 6
7 THEN
8 SIGNAL SQLSTATE '45000';
9 SET @MESSAGE_TEXT = 'El nombre no puede ser menor a 6 caracteres';
10 END IF;
11 END
De esta forma impedimos que se inserten valores no deseados y/o que no comparten la lógica deseada a la vez que generamos un mecanismo para detectar este tipo de errores para poderlos manejar desde nuestra aplicación en cuestión.