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; 

Trigger più complessi

I trigger possono consistere di più istruzioni racchiuse tra BEGIN e END. Se si stanno inserendo queste istruzioni dalla riga di comando, è necessario impostare temporaneamente un nuovo delimitatore, per poter utilizzare i punto e virgola come delimitatore per le istruzioni che compongono il 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)

Errori nei trigger

Se un trigger contiene un errore e lo Storage Engine è transazionale, il trigger non si esegue, e non viene eseguita nemmeno l'istruzione originale. Se l'Engine non è transazionale, il trigger non si esegue, ma l'istruzione originale sì.

Qui di seguito abbandoniamo gli esempi precedenti, e ricreiamo il trigger con un errore, cioè un campo che non esiste, utilizzando prima lo Storage Engine predefinito InnoDB, che è transazionale, e poi usando MyISAM, uno Storage Engine non transazionale.

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)

E ora la stessa procedura, ma su una tabella MyISAM.

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)