Disparadors / triggers a MySQL

Introducció

NOTA. Per crear triggers (igual que passava amb les functions) s'ha de tenir permisos de SUPER. Evidentment l'usuari root té aquests permisos, i per tant pots crear els teus triggers amb l'usauri root. Si vols crear els triggers amb l'usuari alumne se li han d'atorgar els permisos:

mysql> GRANT SUPER ON *.* TO alumne@localhost

(aquesta sentència s'ha d'executar des de l'usuari root, evidentment). Per crear procediments això no era necessari.

• https://www.mysqltutorial.org/mysql-triggers/

A MySQL, un trigger és un procediment emmagatzemat que s'invoca automàticament en resposta a un event com ara un insert, update o delete sobre una taula associada. Per exemple, pots definir un trigger que s'invoca autoàticament abans que una nova fila s'insereixi sobre una taula.

A MySQL els triggers suporten events que s'invoquen en resposta a events tipus INSERT, UPDATE o DELETE.

A MySQL només estan suportats els triggers a nivell de fila, no a nivell de sentència. Això vol dir que si un delete o update afecta a 10 files, s'executarà 10 vegades el trigger, un per cada fila afectada. Els triggers a nivell de sentència no estan suportats (això seria fer un delete que afecta a 10 files, però només s'executa un trigger).

Creació del trigger

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE| DELETE }
ON table_name FOR EACH ROW
trigger_body;

Treballarem amb la taula employees de la base de dades classicmodels.

Volem guardar els canvis que es facin en la taula employees. És a dir, volem auditar aquesta taula. Creem la taula employees_audit:

CREATE TABLE employees_audit (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employeeNumber INT NOT NULL,
    lastname VARCHAR(50) NOT NULL,
    changedat DATETIME DEFAULT NULL,
    action VARCHAR(50) DEFAULT NULL
);

I ara creem el següent trigger:

CREATE TRIGGER before_employee_update 
    BEFORE UPDATE ON employees
    FOR EACH ROW 
 INSERT INTO employees_audit
 SET action = 'update',
     employeeNumber = OLD.employeeNumber,
     lastname = OLD.lastname,
     changedat = NOW();

SHOW TRIGGERS;

Si ara fem un update sobre empleats, veurem com s'ha fet una inserció sobre la taula d'auditació:

UPDATE employees SET lastName = 'Phan' WHERE employeeNumber = 1056;

SELECT * FROM employees_audit;

DROP TRIGGER

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;

CREATE TABLE billings (
    billingNo INT AUTO_INCREMENT,
    customerNo INT,
    billingDate DATE,
    amount DEC(10 , 2 ),
    PRIMARY KEY (billingNo)
);

Creem un trigger:

DELIMITER $$
CREATE TRIGGER before_billing_update
    BEFORE UPDATE 
    ON billings FOR EACH ROW
BEGIN
    IF new.amount > old.amount * 10 THEN
        SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = 'New amount cannot be 10 times greater than the current amount.';
    END IF;
END$$    
DELIMITER ;

SHOW TRIGGERS;

DROP TRIGGER before_billing_update;

SHOW TRIGGERS;

---

creat per Joan Quintana Compte, maig 2022