• Inicio
• Artículos
  • Por categoría
  • Los últimos publicados
  • Los más leidos
  • Leer formato en RSS
• Foros
  • Ir a los foros
  • Últimos publicados
  • Los que estan sin responder
• Enlaces
  • Por categoría
  • Los últimos publicados
  • Los más visitados
• Colaboradores
  • Colaboradores destacados
  • Colaboradores registrados
  • Con más inicios de sesión
• Manuales
  • Por categoría
  • Los últimos publicados
  • Los más descargados
  • Leer formato en RSS
• Códigos fuentes
  • Por lenguaje de programación
  • Los últimos publicados
  • Los más descargados
  • Leer formato en RSS
• Notas de interes
  • Notas por temas
  • Las últimas publicadas
  • Las más leidas
  • Leer formato en RSS

Inicio | Artículos | Mysql | 753

A través de algunos ejemplos y situaciones se da una breve introducción al uso de Triggers en MySQL

Una de las principales características que han sido incorporadas en la versión 5.0 de MySQL es algo llamado "Triggers". Los triggers son objetos relacionados a tablas que son ejecutados o mostrados cuando sucede algún evento en contra de sus tablas asociadas. Estos eventos son aquellas sentencias (INSERT, DELETE, UPDATE) que modifican los datos dentro de la tabla a la que está asociado el trigger y pueden ser disparados antes (BEFORE) y/o después (AFTER) de que la fila es modificada.

Los triggers son muy parecidos a los procedimientos almacenados, de tal forma que si deseamos ejecutar múltiples acciones cuando un trigger es disparado, podemos encapsular estas acciones dentro de una construcción BEGIN, END. Los triggers tienen un par de palabras clave extra - OLD y NEW - las cuales se refieren respectivamente a los valores de las columnas antes y después de que la sentencia fue procesada. Las sentencias INSERT únicamente permiten NEW, las sentencias UPDATE permiten ambos, NEW y OLD, y las sentencias DELETE permiten sólo OLD. La razón para esto debe ser obvia.

Después de dar esta explicación brever acerca de los triggers, la pregunta sería ¿Qué es lo que los triggers pueden hacer por mi?, ¿Para qué nos sirven?. Pues bien, a continuación vamos a mostrar algunos escenarios comunes en los cuales los triggers pueden ser de utilidad.

Una de las preguntas más frecuentes es ¿Cómo puedo saber quién cambió o eliminó una fila? ... Con un trabajo exhaustivo de auditoría sobre la base de datos, o gastando una buena cantidad de tiempo examinando los archivos de registro de MySQL para ver cuando se ejecutó dicha sentencia.

Podemos asignar un trigger a una tabla que se dispare después (AFTER) de una sentencia DELETE o UPDATE, que guarde los valores del registro, así como alguna otra información de utilidad en una tabla de log.

Vamos a examinar un caso práctico, procedemos a ejecutar las siguientes sentencias: CREATE TABLE clientes(
id int not null auto_increment,
nombre varchar(100),
ejecutivo_cuenta varchar(10),
PRIMARY KEY(id),
KEY(nombre)
) ENGINE = InnoDB;

INSERT INTO clientes (nombre, ejecutivo_cuenta) VALUES
('Fredericks','Jaime'),
('Walmart','Mario'),
('Radioshack','Mario'),
('Acme','Juan'),
('Smallco','Jaime');


Ahora tenemos una tabla clientes que guarda la información de clientes y el ejecutivo de cuenta asignado. Cualquiera sabe que la gente de ventas son seres capaces de hacer cualquier cosa por sus intereses y nosotros como administradores de base de datos estamos hartos de las llamadas continuas del departamento de ventas para preguntarnos "QUIEN CAMBIÓ EL EJECUTIVO DE CUENTA DE ESTA COMPAÑIA" !?, y por supuesto involucrarnos en la tarea de descubrir quién cambió los datos.

Es momento de emplear un trigger que venga a nuestro rescate!. Lo primero es crear una tabla simple de log/auditoría: CREATE TABLE auditoria_clientes
(
id int not null auto_increment,
nombre varchar(100),
anterior_ejecutivo_cuenta varchar(10),
usuario varchar(40),
cambiado datetime,
primary key(id)
) ENGINE = InnoDB;


Y ahora crearemos un trigger que vaya llenando los registros de esta tabla cada vez que alguién ejecute una actualización sobre la tabla. Para crear triggers utilizamos la sentencia CREATE TRIGGER: CREATE TRIGGER trigger_auditoria_clientes AFTER UPDATE ON clientes
FOR EACH ROW
BEGIN
INSERT INTO auditoria_clientes(nombre, anterior_ejecutivo_cuenta, usuario, cambiado)
VALUES (OLD.nombre, OLD.ejecutivo_cuenta, CURRENT_USER(), NOW() );
END;

Nota: Para los ejemplos presentados se usará la versión 5.0 más reciente al momento de escribir este artículo, en concreto, la 5.0.10.

Veamos que sucede cuando alguno de los usuarios cambia un registro de la tabla clientes. Por ejemplo, si de una manera "tramposa" Mario quiere hacerse de la cuenta de "Acme", y ejecuta una sentencia de actualización como esta: UPDATE clientes SET ejecutivo_cuenta='Mario' WHERE nombre='Acme'
La codicia de Mario lo supera (o necesita urgentemente dinero para un ingerto de cabello). El piensa que es el vendedor más grandioso desde que Billy vendió su software, y desea hacer de "Acme" uno de sus clientes. Veamos que sucede cuando Mario realiza la actualización (UPDATE). mysql> UPDATE clientes SET ejecutivo_cuenta='Mario' WHERE nombre='Acme';

mysql> SELECT * FROM auditoria_clientes; /* ver nota */
+----+--------+---------------------------+-----------------+---------------------+
| id | nombre | anterior_ejecutivo_cuenta | usuario | cambiado |
+----+--------+---------------------------+-----------------+---------------------+
| 1 | Acme | Juan | Mario@localhost | 2006-01-10 21:59:37 |
+----+--------+---------------------------+-----------------+---------------------+

Como se puede observar, más rápido se descubre a un mentiroso que a un ladrón, el trigger creado anteriormente se activó con la ejecución de la actualización (UPDATE de Mario), y agregó un nuevo registro a la tabla de auditoría. De una forma sencilla sabemos quién realizó una actualización y cuando lo hizo.

Nosotros podemos agregar una columna "accion" a esta tabla de auditoría, e insertar una constante de actualización junto con la inserción para poder tener el registro de las sentencias UPDATE y las sentencias DELETE en esta misma tabla. El trigger para las sentencias DELETE se dispararía con la acción AFTER DELETE.

Nota 1: Lo dicho anteriormente funciona desde MySQL 5.0.10 en adelante. Yo creo que puedes ayudarte bloqueando explicitamente la tabla, o alguna forma de función.

Nota 2: Acerca de la nota del ejercicios, si usted esta ejecutando la consulta como root, en el resultado no va a salir usuario= Mario@localhost, sino root@localhost, asi que deben estar muy atentos si realizan este ejercicio en sus casas u oficinas u cybercafes u cualquier otra parte (a no ser que ustedes hagan la consulta como el usuario Mario o que ustedes sean realmente Mario...).

Una cosa importante que hay que mencionar es que dentro de los triggers se pueden llamar procedimientos (procedures), otra característica que ya tiene MySQL desde la versión 5.0.10 en adelante.

Aquí tenemos una prueba rápida. Vamos a crear una tabla para el registro de todas las ventas, un procedimiento para fijar el porcentaje de comisión para la gente basada en la venta (un rapido ejemplo de procedimiento, y otros vendrán más adelante), y un trigger que llama al procedimiento y fija la comisión después de la inserción a la tabla: mysql> DELIMITER //

mysql> CREATE TABLE ventas (id INT NOT NULL AUTO_INCREMENT,
-> item VARCHAR(10),
-> valor DECIMAL(9,2),
-> ejecutivo_cuenta VARCHAR(10),
-> comision DECIMAL(7,2),
-> PRIMARY KEY(id)) //

mysql> CREATE PROCEDURE comision(valor DECIMAL(9,2))
-> BEGIN
-> SET @comm := valor / 10;
-> END;
-> //

mysql> CREATE TRIGGER ventas_comm BEFORE INSERT ON ventas
-> FOR EACH ROW
-> BEGIN
-> CALL comision(NEW.valor);
-> SET NEW.comision = @comm;
-> END;
-> //

mysql> DELIMITER ;

Ahora insertamos algunas ventas y veamos que sucede:
mysql> INSERT INTO ventas(item, valor, ejecutivo_cuenta) VALUES('Queso',300.00,'Mario');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO ventas(item, valor, ejecutivo_cuenta) VALUES('Papas',400.00,'Mario');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM ventas;
+----+-------+--------+------------------+----------+
| id | item | valor | ejecutivo_cuenta | comision |
+----+-------+--------+------------------+----------+
| 1 | Queso | 300.00 | Mario | 30.00 |
| 2 | Papas | 400.00 | Mario | 40.00 |
+----+-------+--------+------------------+----------+
2 rows in set (0.00 sec)


Vale la pena mencionar que la documentación de MySQL AB relacionada con Triggers tiene que mejorar, porque es bastante pobre.

Ahora, rapidamente otras cosa que se agregaron en MySQL 5 en adelante. Ahora existe una nueva tabla INFORMATION_SCHEMA llamada INFORMATION_SCHEMA.TRIGGERS que entrega informacion como la siguiente: mysql> SELECT trigger_name, action_statement FROM information_schema.triggers
-> WHERE trigger_name = 'ventas_comm';

+---------------+-------------------------------+
| trigger_name| action_statement |
+---------------+-------------------------------+
| ventas_comm| BEGIN|
|| CALL comision(NEW.valor);|
|| SET NEW.comision = @comm ;|
|| END |
+---------------+-------------------------------+
Tu puedes ademas obtener esta informacion, no tan detallada, desde la declaracion SHOW TRIGGERS.