Salta el contingut
 

Disparadors (Triggers)

Joan Puigcerver Ib谩帽ez

j.puigcerveribanez@edu.gva.es

Llic猫ncia: CC BY-NC-SA 4.0

(Reconeixement - NoComercial - CompartirIgual) 馃叚

License

Introducci贸露

Els disparadors o (triggers en angl茅s) s贸n unes rutines que s'executen autom脿ticament quan es produeix un esdeveniment en una taula.

Aquests esdeveniments poden ser:

  • INSERT: quan s'afegeix una nova fila a la taula.
  • UPDATE: quan s'actualitza una fila de la taula.
  • DELETE: quan s'elimina una fila de la taula.

Els disparadors s'identifiquen per un nom 煤nic en la base de dades i s'associen a una taula concreta.

Documentaci贸

Documentaci贸 oficial sobre disparadors:

Els disparadors s贸n 煤tils per a:

  • Validar dades abans d'inserir-les o actualitzar-les.
  • Realitzar tasques autom脿tiques despr茅s d'inserir o actualitzar dades.
  • Auditar els canvis realitzats a la base de dades.
  • Mantenir la consist猫ncia i integritat de les dades.

No obstant aix貌, els disparadors tenen els seg眉ents inconvenients:

  • Poden disminuir el rendiment de la base de dades.
  • Augmenten la complexitat de la base de dades.
  • S贸n dif铆cils de depurar i mantindre.

Creaci贸 d'un disparador露

Per crear un disparador s'utilitza la sent猫ncia CREATE TRIGGER.

Documentaci贸

Documentaci贸 oficial sobre la sent猫ncia CREATE TRIGGER:

CREATE [OR REPLACE]
    TRIGGER [IF NOT EXISTS] trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW
   [{ FOLLOWS | PRECEDES } other_trigger_name ]
   trigger_stmt;
  • trigger_name: nom del disparador.

  • trigger_time: moment en qu猫 s'executar脿 el disparador (BEFORE o AFTER) de l'esdeveniment associat.

  • trigger_event: esdeveniment associat al disparador (INSERT, UPDATE o DELETE).

  • ON tbl_name: nom de la taula a la qual s'associa el disparador.

  • FOR EACH ROW: indica que el disparador s'executar脿 per a cada fila afectada.

    Si la sent猫ncia afecta m茅 de una fila, el disparador s'executar脿 una vegada per a cada fila.

  • FOLLOWS | PRECEDES other_trigger_name: indica l'ordre d'execuci贸 del disparador

  • trigger_stmt: Cos del disparador. Pot ser una 煤nica sent猫ncia SQL o una sent猫ncia composta que comen莽a amb BEGIN i acaba amb END (Vegeu Material: Programaci贸 de bases de dades).

Dades d'exemple

CREATE DATABASE IF NOT EXISTS banc;
USE banc;

CREATE TABLE IF NOT EXISTS compte_bancari (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nom VARCHAR(100) NOT NULL,
  saldo DECIMAL(10, 2) NOT NULL
);

CREATE TABLE historic_moviments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  id_compte INT NOT NULL,
  quantitat DECIMAL(10, 2) NOT NULL,
  data_moviment TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (id_compte) REFERENCES compte_bancari(id)
);

INSERT INTO compte_bancari (nom, saldo) VALUES
  ('Pep', 1000),
  ('Maria', 2000),
  ('Mar', 3000);

Definici贸 disparador registra_moviment

Disparador que registra els canvis de saldo dels comptes bancaris a la taula historic_moviments.

Aquest disparador s'associa a la taula compte_bancari i s'executa AFTER UPDATE, 茅s a dir, despr茅s d'actualitzar una fila de la taula.

DELIMITER //

CREATE TRIGGER registra_moviment
AFTER UPDATE ON compte_bancari
FOR EACH ROW
BEGIN
  -- Sent猫ncies SQL
END //

DELIMITER ;

Variables d'un disparador露

Dins del cos d'un disparador es poden utilitzar les seg眉ents variables:

  • NEW: cont茅 els nous valors de les dades. S'aplica als esdeveniments INSERT (dades inserides) i UPDATE (noves dades).

  • OLD: cont茅 els valors antics de les dades. S'aplica als esdeveniments UPDATE (dades antigues) i DELETE (dades eliminades).

Disparador registra_moviment

Amb les variables NEW i OLD es pot obtindre la quantitat de saldo modificada en un compte bancari.

DELIMITER //

CREATE TRIGGER registra_moviment
AFTER UPDATE ON compte_bancari
FOR EACH ROW
BEGIN
  DECLARE id_compte INT;
  DECLARE saldo_anterior DECIMAL(10, 2);
  DECLARE saldo_actual DECIMAL(10, 2);
  DECLARE quantitat DECIMAL(10, 2);

  SET id_compte = NEW.id;
  SET saldo_anterior = OLD.saldo;
  SET saldo_actual = NEW.saldo;
  SET quantitat = saldo_actual - saldo_anterior;

  INSERT INTO historic_moviments (id_compte, quantitat)
  VALUES (id_compte, quantitat);
END //

DELIMITER ;

Exemple d'execuci贸 del disparador: 

SELECT * FROM compte_bancari;
+----+-------+---------+
| id | nom   | saldo   |
+----+-------+---------+
|  1 | Pep   | 1000.00 |
|  2 | Maria | 2000.00 |
|  3 | Mar   | 3000.00 |
+----+-------+---------+

SELECT * FROM historic_moviments;
Empty set

UPDATE compte_bancari SET saldo = 1500 WHERE id = 1;

SELECT * FROM historic_moviments;
+----+-----------+-----------+---------------------+
| id | id_compte | quantitat | data_moviment       |
+----+-----------+-----------+---------------------+
|  1 |         1 | 500.00    | 2024-03-26 12:00:00 |
+----+-----------+-----------+---------------------+

Excepcions en un disparador露

Els disparadors poden generar errors durant la seva execuci贸, que evitaran que es realitze l'operaci贸 que ha provocat el disparador si s'ha especificat que s'executen abans (BEFORE) de l'esdeveniment.

Les excepcions poden ser llan莽ades amb la sent猫ncia SIGNAL SQLSTATE, que permet especificar el codi d'error i el missatge d'error.

Informaci贸

For user-defined conditions, MariaDB and MySQL recommend the '45000' SQLSTATE class.

Vegeu: https://mariadb.com/kb/en/sqlstate/

SIGNAL SQLSTATE '45000'
  SET MESSAGE_TEXT = 'Missatge d'error';

Disparador comprova_saldo

Volem evitar que es puga retirar m茅s del 50% del saldo d'un compte bancari en una 煤nica operaci贸.

DELIMITER //

CREATE TRIGGER comprova_saldo
BEFORE UPDATE ON compte_bancari
FOR EACH ROW
BEGIN
  DECLARE saldo_minim DECIMAL(10, 2);
  DECLARE saldo_actual DECIMAL(10, 2);

  SET saldo_minim = OLD.saldo / 2;
  SET saldo_actual = NEW.saldo;

  IF saldo_actual < saldo_minim THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'No es pot retirar m茅s del 50% del saldo';
  END IF;
END //

DELIMITER ;

Exemple d'execuci贸 del disparador: 

SELECT * FROM compte_bancari;
+----+-------+---------+
| id | nom   | saldo   |
+----+-------+---------+
|  1 | Pep   | 1000.00 |
|  2 | Maria | 2000.00 |
|  3 | Mar   | 3000.00 |
+----+-------+---------+

UPDATE compte_bancari SET saldo = 800 WHERE id = 1;

SELECT * FROM compte_bancari;
+----+-------+---------+
| id | nom   | saldo   |
+----+-------+---------+
|  1 | Pep   | 800.00  |
|  2 | Maria | 2000.00 |
|  3 | Mar   | 3000.00 |
+----+-------+---------+

UPDATE compte_bancari SET saldo = 200 WHERE id = 1;
ERROR 1644 (45000): No es pot retirar m茅s del 50% del saldo

SELECT * FROM compte_bancari;
+----+-------+---------+
| id | nom   | saldo   |
+----+-------+---------+
|  1 | Pep   | 800.00  |
|  2 | Maria | 2000.00 |
|  3 | Mar   | 3000.00 |
+----+-------+---------+

Bibliografia露

馃搶 Aquest document pot quedar desactualitzat despr茅s d鈥檌mprimir-lo. Pots consultar la versi贸 m茅s recent a la p脿gina web.
馃尶 Abans d鈥檌mprimir aquest document, considera si 茅s realment necessari. Redueix el consum de paper i ajuda a protegir el nostre entorn.
 
CC BY-NC-SA 4.0 (Reconeixement - NoComercial - CompartirIgual) 馃叚 Joan Puigcerver Ib谩帽ez