Capítulo 20. Disparadores (triggers)

MySQL 5.0

Capítulo 20. Disparadores (triggers)

A partir de MySQL 5.0.2 se incorporó el soporte básico 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 dentro de la tabla. El disparador suma los valores insertados en una de las columnas de la tabla:

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
    -> FOR EACH ROW SET @sum = @sum + NEW.amount;

Este capítulo describe la sintaxis para crear y eliminar disparadores, y muestra algunos ejemplos de cómo utilizarlos. Las restricciones en el uso de disparadores se tratan en Apéndice H, Restricciones en características de MySQL.

En Sección 19.3, “Registro binario de procedimientos almacenados y disparadores” se describe la forma en que se realiza el registro binario (binary logging) para los disparadores.

20.1. Sintaxis de CREATE TRIGGER

CREATE TRIGGER   
    ON  FOR EACH ROW 

Un disparador es un objeto con nombre en una base de datos que se asocia con una tabla, y se activa cuando ocurre un evento en particular para esa tabla.

El disparador queda asociado a la tabla . Esta debe ser una tabla permanente, no puede ser una tabla ni una vista.

es el momento en que el disparador entra en acción. Puede ser (antes) o (despues), para indicar que el disparador se ejecute antes o después que la sentencia que lo activa.

indica la clase de sentencia que activa al disparador. Puede ser , , o . Por ejemplo, un disparador para sentencias podría utilizarse para validar los valores a insertar.

No puede haber dos disparadores en una misma tabla que correspondan al mismo momento y sentencia. Por ejemplo, no se pueden tener dos disparadores . Pero sí es posible tener los disparadores y o y .

es la sentencia que se ejecuta cuando se activa el disparador. Si se desean ejecutar múltiples sentencias, deben colocarse entre , el constructor de sentencias compuestas. Esto además posibilita emplear las mismas sentencias permitidas en rutinas almacenadas. Consulte Sección 19.2.7, “Sentencia compuesta .

Note: Antes de MySQL 5.0.10, los disparadores no podían contener referencias directas a tablas por su nombre. A partir de MySQL 5.0.10, se pueden escribir disparadores como el llamado , que se muestra en este ejemplo:

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
  a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  b4 INT DEFAULT 0
);

DELIMITER |

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;  
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END
|

DELIMITER ;

INSERT INTO test3 (a3) VALUES 
  (NULL), (NULL), (NULL), (NULL), (NULL), 
  (NULL), (NULL), (NULL), (NULL), (NULL);

INSERT INTO test4 (a4) VALUES 
  (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

Si en la tabla se insertan los siguientes valores:

mysql> INSERT INTO test1 VALUES 
    -> (1), (3), (1), (7), (1), (8), (4), (4);
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

Entonces los datos en las 4 tablas quedarán así:

mysql> SELECT * FROM test1;
+------+
| a1   |
+------+
|    1 |
|    3 |
|    1 |
|    7 |
|    1 |
|    8 |
|    4 |
|    4 |
+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM test2;
+------+
| a2   |
+------+
|    1 |
|    3 |
|    1 |
|    7 |
|    1 |
|    8 |
|    4 |
|    4 |
+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM test3;
+----+
| a3 |
+----+
|  2 |
|  5 |
|  6 |
|  9 |
| 10 |
+----+
5 rows in set (0.00 sec)

mysql> SELECT * FROM test4;
+----+------+
| a4 | b4   |
+----+------+
|  1 |    3 |
|  2 |    0 |
|  3 |    1 |
|  4 |    2 |
|  5 |    0 |
|  6 |    0 |
|  7 |    1 |
|  8 |    1 |
|  9 |    0 |
| 10 |    0 |
+----+------+
10 rows in set (0.00 sec)

Las columnas de la tabla asociada con el disparador pueden referenciarse empleando los alias y . hace referencia a una columna de una fila existente, antes de ser actualizada o borrada. hace referencia a una columna en una nueva fila a punto de ser insertada, o en una fila existente luego de que fue actualizada.

El uso de = necesita que se tenga el privilegio sobre la columna. El uso de = NEW. necesita el privilegio sobre la columna.

Nota: Actualmente, los disparadores no son activados por acciones llevadas a cabo en cascada por las restricciones de claves extranjeras. Esta limitación se subsanará tan pronto como sea posible.

La sentencia necesita el privilegio . Esto se agregó en MySQL 5.0.2.