<div class="page"> <div class="cover text-center"> <img class="mx-auto" src=/itb/images/logo_mislata.png alt="logo"> # Disparadors (Triggers) <div class="text-end fit-content ms-auto my-3 mt-auto pt-3"> <p><strong>Autor:</strong> Joan Puigcerver Ibáñez</p> <p><strong>Correu electrònic:</strong> j.puigcerveribanez@edu.gva.es</p> <p><strong>Curs:</strong> 2023/2024</p> </div> <div> <p class="fw-bold mb-0">Llicència: BY-NC-SA</p> <p class="d-none d-md-block">(Reconeixement - No Comercial - Compartir Igual)</p> <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/deed.ca" target="_blank"> <img class="mx-auto" src="/itb/images/license.png" alt="Licence"/> </a> </div><!--license--> </div><!--cover--> </div><!--page--> {:toc} ## 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. ::: docs Documentació oficial sobre disparadors: - MariaDB: https://mariadb.com/kb/en/trigger-overview/ - MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html ::: 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`. ::: docs Documentació oficial sobre la sentència `CREATE TRIGGER`: - MariaDB: https://mariadb.com/kb/en/create-trigger/ - MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/create-trigger.html ::: ```sql 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](/itb/DAM-BD/UD7/materials/01_plsql.html)). ::: info "Dades d'exemple" ```sql 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); ``` ::: ::: example "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. ```sql 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). ::: example "Disparador registra_moviment" Amb les variables `NEW` i `OLD` es pot obtindre la quantitat de saldo modificada en un compte bancari. ```sql 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: ```sql 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. ::: info For user-defined conditions, MariaDB and MySQL recommend the '45000' SQLSTATE class. Vegeu: https://mariadb.com/kb/en/sqlstate/ ::: ```sql SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Missatge d'error'; ``` ::: example "Disparador comprova_saldo" Volem evitar que es puga retirar més del 50% del saldo d'un compte bancari en una única operació. ```sql 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: ```sql 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 - https://www.edureka.co/blog/triggers-in-sql/