Les différents types de triggers MySQL

Create table customers(customer_id BIGINT PRIMARY KEY, customer_name VARCHAR(50), level VARCHAR(50) ) ENGINE=INNODB;

Insert into customers (customer_id, customer_name, level )values('1','JOHN DOE','BASIC');
Insert into customers (customer_id, customer_name, level )values('2','MARY ROE','BASIC');
Insert into customers (customer_id, customer_name, level )values('3','JOHN DOE','VIP');

Create table customer_status(customer_id BIGINT PRIMARY KEY, status_notes VARCHAR(50)) ENGINE=INNODB;

Create table sales(sales_id BIGINT PRIMARY KEY, customer_id BIGINT, sales_amount DOUBLE ) ENGINE=INNODB;

Create table audit_log(log_id BIGINT PRIMARY KEY AUTO_INCREMENT, sales_id BIGINT, previous_amount DOUBLE, new_amount DOUBLE, 
updated_by VARCHAR(50), updated_on DATETIME ) ENGINE=INNODB;

• Ce trigger sera associé à la table des ventes sales et il sera invoqué avant l’insertion d’un enregistrement (Before Insert) pour valider le sales_amount.

DELIMITER //
CREATE TRIGGER validate_sales_amount
BEFORE INSERT
ON sales
FOR EACH ROW
IF NEW.sales_amount>10000 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'La vente a dépassé le montant autorisé de 10 000.';
END IF//
DELIMITER ;

Vous utilisez l’instruction IF... THEN... END IF pour évaluer si la quantité fournie lors de l’instruction INSERT est comprise dans votre plage.

Le trigger est capable d’extraire la nouvelle valeur sales_amount fournie en utilisant le mot-clé NEW.

• Les triggers AFTER INSERT sont exécutés lorsque les enregistrements sont insérés avec succès dans une table. Cette fonctionnalité peut être utilisée pour exécuter automatiquement d’autres logiques commerciales.
• Par exemple, dans une application bancaire, un déclencheur AFTER INSERT peut fermer un compte de prêt lorsqu’un client a fini de rembourser le prêt.
• Le trigger peut surveiller tous les paiements insérés dans une table de transactions et fermer le prêt automatiquement une fois que le solde du prêt est à zéro.
• Dans cette étape, vous travaillerez avec votre table « customer_status » en utilisant un trigger AFTER INSERT pour entrer des enregistrements de clients apparentés.
• Pour créer le trigger AFTER INSERT, entrez les commandes suivantes :

DELIMITER //
CREATE TRIGGER customer_status_records
AFTER INSERT
ON customers
FOR EACH ROW
Insert into customer_status(customer_id, status_notes) VALUES(NEW.customer_id, 'ACCOUNT OPENED SUCCESSFULLY')//
DELIMITER ;

insérez un nouvel enregistrement dans la table customers pour confirmer que votre code de trigger sera invoqué :Insert into customers (customer_id, customer_name, level )values(‘4′,’DAVID DOE’,’VIP’);



La sortie confirme que le trigger a bien été exécuté.

DELIMITER //
CREATE TRIGGER validate_customer_level
BEFORE UPDATE
ON customers
FOR EACH ROW
IF OLD.level='VIP' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Un client VIP ne peut pas être déclassé.';
END IF //
DELIMITER ;

Vous utilisez le mot-clé OLD pour capturer le niveau fourni par l’utilisateur lors de l’exécution de la commande UPDATE.

Encore une fois, vous utilisez l’instruction IF... THEN... END IF pour signaler à l’utilisateur une instruction d’erreur générique.

Exécutez la commande SQL suivante qui tente de rétrograder un compte client associé au customer_id 3

Update customers set level=’BASIC’ where customer_id=’3′;

Vous verrez la sortie suivante.

DELIMITER //
CREATE TRIGGER log_sales_updates
AFTER UPDATE
ON sales
FOR EACH ROW
Insert into audit_log(sales_id, previous_amount, new_amount, updated_by, updated_on) VALUES (NEW.sales_id,OLD.sales_amount, NEW.sales_amount,(SELECT USER()), NOW() )//
DELIMITER ;

• Vous insérez un nouvel enregistrement dans la table audit_log. Vous utilisez le mot-clé NEW pour récupérer la valeur du sales_id et du nouveau sales_amount En outre, vous utilisez le mot-clé OLD pour récupérer le sales_amount précédent puisque vous voulez enregistrer les deux montants à des fins d’audit.
• La commande SELECT User() récupère l’utilisateur actuel de l’opération et l’instruction NOW() extrait la valeur de la date et de l’heure actuelle du serveur MySQL
• Maintenant, si un utilisateur essaie de mettre à jour la valeur d’un enregistrement dans la table sales, le trigger log_sales_updates va insérer un nouvel enregistrement dans la table audit_log.
• Créons un nouvel enregistrement de ventes avec un sales_id aléatoire de 5 et essayons de le mettre à jour. En premier lieu, insérez l’enregistrement des ventes avec :
  Insert into sales(sales_id, customer_id, sales_amount) values(‘5’, ‘2’,’8000′);
• Ensuite, mettez à jour l’enregistrement :
  Update sales set sales_amount=’9000′ where sales_id=’5′;
• Le trigger a enregistré la mise à jour. Votre sortie indique l’ancien et le nouveau sales_amount enregistrés avec l’utilisateur qui a mis à jour les enregistrements :

DELIMITER //
CREATE TRIGGER delete_related_info
AFTER DELETE
ON sales
FOR EACH ROW
Delete from customers where customer_id=OLD.customer_id;//
DELIMITER ;