MariaDB : création d’un trigger AFTER
18 février 2023
Le trigger ci-dessous est une sorte de mouchard qui trace les modifications de prix ou de remise à partir de la table order_details de la base northwind, dont voici le script de création de table :
CREATE TABLE northwind.order_details ( id INT(11) NOT NULL AUTO_INCREMENT, order_id INT(11) NOT NULL, product_id INT(11) NULL DEFAULT NULL, quantity DECIMAL(18,4) NOT NULL DEFAULT '0.0000', unit_price DECIMAL(19,4) NULL DEFAULT '0.0000', discount DOUBLE NOT NULL DEFAULT '0', status_id INT(11) NULL DEFAULT NULL, date_allocated DATETIME NULL DEFAULT NULL, purchase_order_id INT(11) NULL DEFAULT NULL, inventory_id INT(11) NULL DEFAULT NULL, );
Les modifications sur les colonnes unit_price et discount seront consignées dans la table log.log_update :
CREATE TABLE log.log_update(
id_key INT NULL,
column_name VARCHAR(1024) NULL,
table_name VARCHAR(1024) NULL,
old_value FLOAT NULL,
new_value FLOAT NULL
);
Le code du trigger AFTER
USE northwind; -- https://mariadb.com/kb/en/programmatic-compound-statements/ DELIMITER $$ CREATE OR REPLACE TRIGGER northwind.trg_order_details_after_insert AFTER UPDATE ON northwind.order_details FOR EACH ROW BEGIN IF OLD.discount<>NEW.discount THEN INSERT INTO log.log_update(id_key,COLUMN_NAME,TABLE_NAME,old_value,new_value) VALUES(OLD.id,'discount','northwind.order_details',OLD.discount,NEW.discount); END IF; IF OLD.unit_price<>NEW.unit_price THEN INSERT INTO log.log_update(id_key,COLUMN_NAME,TABLE_NAME,old_value,new_value) VALUES(OLD.id,'unit_price','northwind.order_details',OLD.unit_price,NEW.unit_price); END IF; END; $$ DELIMITER ;
ddddd