Trigger Overview

Un trigger, come suggerisce il nome (grilletto), è un insieme di istruzioni che si eseguono, o vengono attivate, quando un dato evento avviene su una tabella.

L'evento può essere una INSERT, una UPDATE o una DELETE.

Creare un trigger

Ecco un semplice esempio per vedere un trigger in azione. Utilizzeremo come esempio queste due tabelle:

MariaDB [test]> CREATE TABLE animals (id mediumint(9) 
NOT NULL AUTO_INCREMENT, 
name char(30) NOT NULL, 
PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.29 sec)

CREATE TABLE animal_count (animals int);

INSERT INTO animal_count (animals) VALUES(0);

Vogliamo incrementare un contatore ogni volta che un animale viene aggiunto. Ecco il trigger necessario:

MariaDB [test]> CREATE TRIGGER increment_animal 
AFTER INSERT ON animals 
FOR EACH ROW 
UPDATE animal_count SET animal_count.animals = animal_count.animals+1;

Il trigger ha:

• un nome (in questo caso increment_animal)
• una tempistica (in questo caso after, dopo l'evento specificato)
• un evento (una INSERT)
• una tabella al quale è associato (animals)
• un insieme di istruzioni da eseguire (in questo caso una sola istruzione UPDATE)

AFTER INSERT specifica che il trigger deve essere eseguito dopo una INSERT. Il trigger può anche essere impostato per agire prima, e l'istruzione che aziona il trigger può anche essere una DELETE o una UPDATE.

Ora, se inseriamo un record nella tabella animals, il trigger verrà eseguito, incrementando la tabella animal_count;

MariaDB [test]> SELECT * FROM animal_count;
+---------+
| animals |
+---------+
|       0 |
+---------+

MariaDB [test]> INSERT INTO animals (name) VALUES('aardvark');
MariaDB [test]> INSERT INTO animals (name) VALUES('baboon');

MariaDB [test]> SELECT * FROM animal_count;
+---------+
| animals |
+---------+
|       2 |
+---------+

Per ulteriori dettagli sulla sintassi, si veda CREATE TRIGGER.

Eliminare i trigger

Per eliminare un trigger, si usa l'istruzione DROP TRIGGER. I trigger inoltre vengono eliminati se si elimina la tabella alla quale sono associati.

MariaDB [test]> DROP TRIGGER increment_animal; 

More complex triggers

Triggers can consist of multiple statements enclosed by a BEGIN and END. If you're entering multiple statements on the command line, you'll want to temporarily set a new delimiter so that you can use a semicolon to delimit the statements inside your trigger.

MariaDB [test]> DROP TABLE animals;

MariaDB [test]> UPDATE animal_count SET animals=0;

MariaDB [test]> CREATE TABLE animals (id mediumint(9) NOT NULL AUTO_INCREMENT, 
name char(30) NOT NULL, 
PRIMARY KEY (`id`)) 
ENGINE=InnoDB;

MariaDB [test]> DELIMITER //
CREATE TRIGGER the_mooses_are_loose
AFTER INSERT ON animals
FOR EACH ROW
BEGIN
 IF NEW.name = 'Moose' THEN
  UPDATE animal_count SET animal_count.animals = animal_count.animals+100;
 ELSE 
  UPDATE animal_count SET animal_count.animals = animal_count.animals+1;
 END IF;
END; //

MariaDB [test]> DELIMITER ;

MariaDB [test]> INSERT INTO animals (name) VALUES('Aardvark');

MariaDB [test]> SELECT * FROM animal_count;
+---------+
| animals |
+---------+
|       1 |
+---------+

MariaDB [test]> INSERT INTO animals (name) VALUES('Moose');

MariaDB [test]> SELECT * FROM animal_count;
+---------+
| animals |
+---------+
|     101 |
+---------+
1 row in set (0.00 sec)

Trigger errors

If a trigger contains an error and the engine is transactional, the trigger will not run, and will prevent the original statement from running as well. If the engine is non-transactional, the trigger will not run, but the original statement will.

Here, we'll drop the above examples, and then recreate the trigger with an error, a field that doesn't exist, first using the default InnoDB, a transactional engine, and then again using MyISAM, a non-transactional engine.

MariaDB [test]> DROP TABLE animals;

MariaDB [test]> CREATE TABLE animals (id mediumint(9) NOT NULL AUTO_INCREMENT, 
name char(30) NOT NULL, 
PRIMARY KEY (`id`)) 
ENGINE=InnoDB;

MariaDB [test]> CREATE TRIGGER increment_animal 
AFTER INSERT ON animals 
FOR EACH ROW 
UPDATE animal_count SET animal_count.id = animal_count_id+1;

MariaDB [test]> INSERT INTO animals (name) VALUES('aardvark');
ERROR 1054 (42S22): Unknown column 'animal_count.id' in 'field list'

MariaDB [test]> SELECT * FROM animals;
Empty set (0.00 sec)

And now the identical procedure, but with a MyISAM table.

MariaDB [test]> DROP TABLE animals;

MariaDB [test]> CREATE TABLE animals (id mediumint(9) NOT NULL AUTO_INCREMENT, 
name char(30) NOT NULL, 
PRIMARY KEY (`id`)) 
ENGINE=MyISAM;

MariaDB [test]> CREATE TRIGGER increment_animal 
AFTER INSERT ON animals 
FOR EACH ROW 
UPDATE animal_count SET animal_count.id = animal_count_id+1;

MariaDB [test]> INSERT INTO animals (name) VALUES('aardvark');
ERROR 1054 (42S22): Unknown column 'animal_count.id' in 'field list'

MariaDB [test]> SELECT * FROM animals;
+----+----------+
| id | name     |
+----+----------+
|  1 | aardvark |
+----+----------+
1 row in set (0.00 sec)