Salta el contingut

6. Triggers

6.1. Introducció

Un disparador és un bloc de codi, associat a una taula, que s'executa abans o després que passe una acció concreta d'inserció, esborrat o modificació sobre eixa taula.

Per exemple, donada la següent taula:

SQL
1
CREATE TABLE comptes (num INT, saldo DECIMAL (10,2));

Volem fer que cada vegada que s'inserisca un nou compte, que s'acumule en una variable el saldo dels comptes. Per a fer això crearem el següent disparador:

SQL
1
2
3
CREATE TRIGGER abans_ins_compte
BEFORE INSERT ON comptes
     FOR EACH ROW SET @suma = @suma + NEW.saldo;

Hem definit el disparador de nom abans_ins_compte així: "Abans d'inserir en la taula comptes, per cada fila a inserir, acumula el nou saldo en la variable @suma".

Com que @suma fa d'acumulador, abans d'usar-lo caldrà inicialitzar-lo a 0:

SQL
1
2
3
4
SET @suma = 0;
INSERT INTO comptes VALUES (1, 100), (2, 150), (3, 200);
INSERT INTO comptes VALUES (4, 250);
SELECT @suma;   -- Mostrarà la suma total: 700

6.2. Sintaxi de CREATE TRIGGER

SQL
1
2
3
CREATE TRIGGER [nom_bd.]nom_disparador
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON nom_taula
FOR EACH ROW sentència

Sobre la sentència

  • La sentència pot ser composta (bloc BEGIN-END). En eixe cas, caldrà indicar que el delimitador de sentències no siga el ";", amb DELIMITER.
  • En la sentència podran haver sentències condicionals, bucles...

Nota

Quan es fa una acció sobre una taula, primer s'executa el bloc associat al BEFORE, després la pròpia acció (insert, update, delete) i després el bloc associat al AFTER. Per tant, si donara error alguna d'estes accions, no s'executarien les següents.

Per a esborrar-lo:

SQL
1
DROP TRIGGER [nom_bd.] nom_disparador;

6.3. Els àlies OLD i NEW

En la sentència que s'executarà per a cada registre afectat, es pot fer referència als valors d'este registre abans i/o després de fer una acció d'UPDATE/INSERT/DELETE:

Acció OLD NEW
UPDATE Valor del camp abans de ser modificat Valor del camp després de ser modificat
INSERT (no té sentit) Valor que s'insereix en eixe camp
DELETE Valor del camp del registre que s'esborra (no té sentit)

6.4. Limitacions dels disparadors

  • Limitacions en els noms:
    • Una taula no pot tindre dos disparadors amb el mateix nom. I és convenient que en una mateixa BD no es repetisquen els noms dels disparadors.
  • Limitacions en els tipus:
    • En una taula no poden haver dos disparadors del mateix tipus. Per exemple, no pot tindre dos disparadors BEFORE UPDATE.
  • Limitacions en la sentència:
    • No es pot usar el CALL (invocar procediments emmagatzemats), però sí invocar a funcions.
    • No es poden iniciar o acabar transaccions (START TRANSACTION, COMMIT o ROLLBACK).
    • En BEFORE INSERT, el valor NEW per a una columna AUTO_INCREMENT és 0.
    • Permisos:
      • Per a fer SET NEW.nom_camp = valor cal tindre permís d'UPDATE sobre la columna nom_camp (i fer-ho en triggers de tipus BEFORE).
      • Per a fer SET nom_var = NEW.nom_camp cal tindre permís de SELECT sobre la columna nom_camp.

6.5. Algunes utilitats

  • Actualitzar camps de taules que depenen del valor d'altres camps: import en una línia de factura, el total d'una factura...
  • Modificar els valors a introduir en la taula (amb BEFORE INSERT o BEFORE UPDATE):

Exemple: controlar que una nota estiga entre 0 i 10

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
delimiter //
CREATE TRIGGER nota_ok BEFORE UPDATE ON notes
FOR EACH ROW
    BEGIN
    IF NEW.nota < 0 THEN
        SET NEW.nota = 0;
    ELSEIF NEW.nota > 10 THEN
        SET NEW.nota = 10;
    END IF;
    END; //
delimiter ;

Nota

Caldria repetir el mateix per a BEFORE INSERT. I estaria bé poder posar les sentències associades en un procediment emmagatzemat, però MySQL no permet fer ús del CALL en els disparadors.

6.6. Exercicis de triggers

10. En la taula de golejadors, entre altres camps estan: gols, gtitular i gsuplent. Realment el camp de gols no hauria d'estar ja que es pot calcular com la suma dels altres dos, però està per optimitzar consultes, etc.

  • a) Fes els triggers necessaris per a assegurar que el camp gols sempre serà la suma dels altres dos.
  • b) Fes alguna inserció en la taula de golejadors i alguna modificació, posant aposta els gols que no sumen els gols com a titular i com a suplent. Després comprova que ha funcionat: mira si els gols són igual a la suma dels altres dos camps.

11. Volem tindre en la taula d'equips un camp que guarde la quantitat de defenses que té cada equip (encara que sabem que serà un camp redundant, ja que es podria calcular a partir de la taula de jugadors). Per a això, fes el següent:

  • a) Afig a la taula d'equips el camp qdefenses.
  • b) Ompli els valors d'eixa columna per a cada equip existent en la base de dades.
  • c) Fes els triggers necessaris per a mantindre sempre actualitzat eixe camp.
  • d) Fes canvis amb els defenses de la base de dades per a vore que els triggers funcionen.

12. (BD geografia) Volem tindre en la taula de rius un camp que guarde la quantitat de províncies per on passa cadascun (encara que sabem que serà un camp redundant, ja que es podria calcular a partir de la taula passaper). Per a això, fes el següent:

  • a) Afig a la taula de rius el camp qprov.
  • b) Ompli els valors d'eixa columna per a cada riu existent en la base de dades.
  • c) Fes els triggers necessaris per a mantindre sempre actualitzat eixe camp.
  • d) Fes canvis amb els rius de la base de dades per a vore que els triggers funcionen.