Les déclencheurs (Trigger)

Les déclencheurs (triggers) en MySQL sont des éléments de base de données qui permettent d'automatiser des actions en réponse à des événements spécifiques, tels que l'insertion (insert), la mise à jour (update) ou la suppression (delete) de données dans une table.
Les déclencheurs sont associés à une table et sont déclenchés automatiquement lorsque l'événement(select,update,delete) spécifié se produit.
Ils sont souvent utilisés pour

Garantir l'intégrité des données
Effectuer des audits
Générer des journaux
Effectuer d'autres actions automatisées en réponse à des modifications de données
...

Types de déclencheurs :

Les déclencheurs "BEFORE" sont déclenchés avant l'exécution de la commande(select,update ,delete) qui a déclenché le déclencheur.
Ils permettent d'intercepter et de modifier les données avant qu'elles ne soient écrites dans la table.

Les déclencheurs "AFTER" sont déclenchés après l'exécution de la commande, ce qui signifie qu'ils ne peuvent pas modifier les données, mais ils peuvent effectuer des actions en fonction des données modifiées.

Événements déclencheurs:

L'événement déclencheur (INSERT, UPDATE, DELETE).
Le moment de déclenchement (BEFORE ou AFTER).

Sytaxe:

CREATE TRIGGER nom_du_declencheur
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON nom_de_la_table
FOR EACH ROW
BEGIN
    -- Corps du déclencheur : instructions SQL
END;

CREATE TRIGGER : Définit le début de la commande de création du déclencheur.
nom_du_declencheur : Spécifie le nom du déclencheur que vous créez. Le nom doit être unique au sein de la base de données.
{BEFORE | AFTER} : Indique si le déclencheur doit être déclenché avant ou après l'événement spécifié (INSERT, UPDATE, DELETE).
{INSERT | UPDATE | DELETE} : Décrit l'événement qui déclenchera le déclencheur.
ON nom_de_la_table : Indique la table à laquelle le déclencheur est associé.
FOR EACH ROW : Cette clause spécifie que le déclencheur est déclenché pour chaque ligne affectée par la commande, c'est-à-dire pour chaque enregistrement.
BEGIN ... END : C'est l'emplacement où vous placez le corps du déclencheur, c'est-à-dire les instructions SQL que vous souhaitez exécuter lorsque le déclencheur est activé. Les instructions SQL peuvent être complexes et inclure des conditions, des mises à jour, des inserts, etc.

Exemples:

Un déclencheur BEFORE INSERT pour vérifier que le prix du produit est positif.
Un déclencheur BEFORE UPDATE pour vérifier que la quantité en stock n'est pas négative.
Un déclencheur BEFORE DELETE pour empêcher la suppression de produits liés à des ventes.

Exemple1

Ecrire un trigger qui permet d'insérer une copie de sauvegarde de la table client '

create Trigger copieClient
after insert on client
for each row
begin
insert into ClientSauvgarde(idcli,nom,ville)
values(NEW.idCli,NEW.nom,NEW.ville);
end;

OLD ,NEW

BEFORE INSERT Trigger : Utiliser "NEW" pour accéder aux valeurs des données qui seront insérées dans la ligne.
BEFORE UPDATE Trigger : Utiliser "OLD" pour accéder aux valeurs actuelles (avant la mise à jour) et "NEW" pour accéder aux valeurs mises à jour (après la mise à jour).
BEFORE DELETE Trigger : Utiliser "OLD" pour accéder aux valeurs des données avant la suppression.
AFTER INSERT, AFTER UPDATE, AFTER DELETE Trigger : Dans ces déclencheurs, "NEW" et "OLD" sont généralement utilisés pour effectuer des opérations postérieures à l'opération, telles que l'audit des changements ou la mise à jour de certaines données.

Exemple2

Créer un trigger qui permet de stocker la date de la modification du mot de passe

create trigger SaveMotpasse 
after update on utilisateur
begin
insert into motpasse(iduser,email,oldpass,newpass,dateModification)
        values(OLD.iduser,OLD.email,OLD.oldpass,NEW.newpass,now());
	end;

Exemple3

Gestion des exception avec les trigger

Create trigger before_insert_studentage
BEFORE INSERT on student_age
FOR EACH ROW 
BEGIN 
IF NEW.age<0 then --la condition pour délencher l'exception
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'age less than 0';
END if; END;


Signal :Permet de sortir du trigger avec un message d'erreur '
     SQLSTATE '45000':le code du message d'erreur 
          ,doit etre >=45000 '
	SET MESSAGE_TEXT = 'le message d erreur';

Exemple4

écrire un trigger qui permet d'interdire l'ajout< de nouveau de client de casa
les clients de casa n'ont pas le droit

create trigger Noncasa
before insert ,update  on client
for each row 
begin
if NEW.ville='casa' then
SIGNAL SQLSTATE '45000' 
SET MESSAGE_TEXT = 'les clients de casa n ont pas le droit';
end if;
end;

Exemple5

Ecrire un trigger qui permet d'interdire la suppression d'un client qui a déja acheté quelque chose

create trigger DontDeleteClient 
before delete on Client
for each row
begin
declare n int default 0;
select count(v.idcli) from vente v 
where v.idcli=OLD.idcli into n;
if n>0 then
 SIGNAL SQLSTATE '45000' 
SET MESSAGE_TEXT = 'impossible de supprimer ce client';
end if;
end;

Exemples

Q : Déclencheur BEFORE INSERT pour vérifier que le prix du produit est positif (table "produits")

DELIMITER //
-- Crée un déclencheur BEFORE INSERT nommé "check_positive_price" sur la table "produits"
CREATE TRIGGER check_positive_price BEFORE INSERT ON produits
FOR EACH ROW
BEGIN
    -- Vérifie si le prix du produit est inférieur ou égal à zéro
    IF NEW.prix <= 0 THEN
        -- Si le prix est négatif ou nul, signale une erreur avec un message personnalisé
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Le prix ne peut pas être négatif ou nul.';
    END IF;
END;
//
DELIMITER ;

Q : Déclencheur BEFORE UPDATE pour vérifier que la quantité en stock n'est pas négative (table "produits")

DELIMITER //
-- Crée un déclencheur BEFORE UPDATE nommé "check_non_negative_stock" sur la table "produits"
CREATE TRIGGER check_non_negative_stock BEFORE UPDATE ON produits
FOR EACH ROW
BEGIN
    -- Vérifie si la quantité en stock deviendrait négative après la mise à jour
    IF NEW.quantiteStock < 0 THEN
        -- Si la quantité en stock serait négative, signale une erreur avec un message personnalisé
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La quantité en stock ne peut pas devenir négative.';
    END IF;
END;
//
DELIMITER ;

Q : Déclencheur BEFORE DELETE pour empêcher la suppression de produits liés à des ventes (table "produits")

DELIMITER //
-- Crée un déclencheur BEFORE DELETE nommé "prevent_delete_product" sur la table "produits"
CREATE TRIGGER prevent_delete_product BEFORE DELETE ON produits
FOR EACH ROW
BEGIN
    -- Vérifie si le produit est lié à des ventes en cours
    DECLARE vente_count INT;
    SELECT COUNT(*) INTO vente_count FROM ventes WHERE idProduit = OLD.id;
    IF vente_count > 0 THEN
        -- Si le produit est lié à des ventes, signale une erreur avec un message personnalisé
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Impossible de supprimer le produit, il est lié à des ventes.';
    END IF;
END;
//
DELIMITER ;

Q Déclencheur BEFORE INSERT pour valider le numéro de téléphone du client (table "clients")

DELIMITER //
-- Crée un déclencheur BEFORE INSERT nommé "validate_phone_number" sur la table "clients"
CREATE TRIGGER validate_phone_number BEFORE INSERT ON clients
FOR EACH ROW
BEGIN
    -- Vérifie si le numéro de téléphone du client est valide (par exemple, un format spécifique)
    IF NEW.tel NOT REGEXP '^[0-9]{10}$' THEN
        -- Si le numéro de téléphone n'est pas valide, signale une erreur avec un message personnalisé
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Numéro de téléphone invalide.';
    END IF;
END;
//
DELIMITER ;

Q Déclencheur AFTER INSERT pour ajouter une entrée à un journal des nouvelles inscriptions (table "clients")

DELIMITER //
-- Crée un déclencheur AFTER INSERT nommé "log_new_registration" sur la table "clients"
CREATE TRIGGER log_new_registration AFTER INSERT ON clients
FOR EACH ROW
BEGIN
    -- Insère une entrée dans une table de journal pour enregistrer la nouvelle inscription
    INSERT INTO journal_des_inscriptions (client_id, date_inscription)
    VALUES (NEW.id, NOW());
END;
//
DELIMITER ;

Q Déclencheur BEFORE UPDATE pour empêcher la modification de la date de naissance (table "clients")

DELIMITER //
-- Crée un déclencheur BEFORE UPDATE nommé "prevent_dob_change" sur la table "clients"
CREATE TRIGGER prevent_dob_change BEFORE UPDATE ON clients
FOR EACH ROW
BEGIN
    -- Vérifie si la date de naissance a été modifiée
    IF NEW.dateNaissance <> OLD.dateNaissance THEN
        -- Si la date de naissance a été modifiée, signale une erreur avec un message personnalisé
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La date de naissance ne peut pas être modifiée.';
    END IF;
END;
//
DELIMITER ;

Q Déclencheur BEFORE INSERT pour calculer le montant total de la vente en fonction du prix du produit et de la quantité vendue (table "ventes")

DELIMITER //
-- Crée un déclencheur BEFORE INSERT nommé "calculate_total_amount" sur la table "ventes"
CREATE TRIGGER calculate_total_amount BEFORE INSERT ON ventes
FOR EACH ROW
BEGIN
    -- Calcule le montant total de la vente
    SET NEW.prixVente = NEW.QuantiteVendue * (SELECT prix FROM produits WHERE id = NEW.idProduit);
END;
//
DELIMITER ;

Q Déclencheur BEFORE INSERT pour vérifier si le produit est en promotion et appliquer une remise le cas échéant (table "ventes")

DELIMITER //
-- Crée un déclencheur BEFORE INSERT nommé "apply_discount" sur la table "ventes"
CREATE TRIGGER apply_discount BEFORE INSERT ON ventes
FOR EACH ROW
BEGIN
    -- Vérifie si le produit est en promotion
    DECLARE is_promotion TINYINT(1);
    SET is_promotion = (SELECT promotion FROM produits WHERE id = NEW.idProduit);
    
    -- Applique une remise de 10 % si le produit est en promotion
    IF is_promotion = 1 THEN
        SET NEW.remise = NEW.prixVente * 0.1;
    END IF;
END;
//
DELIMITER ;

Q Déclencheur AFTER INSERT pour mettre à jour la quantité en stock du produit vendu (table "ventes")

 DELIMITER //
-- Crée un déclencheur AFTER INSERT nommé "update_stock_quantity" sur la table "ventes"
CREATE TRIGGER update_stock_quantity AFTER INSERT ON ventes
FOR EACH ROW
BEGIN
    -- Met à jour la quantité en stock du produit vendu
    UPDATE produits
    SET quantiteStock = quantiteStock - NEW.QuantiteVendue
    WHERE id = NEW.idProduit;
END;
//
DELIMITER ;

Q Déclencheur BEFORE DELETE pour annuler une vente et restaurer la quantité en stock du produit (table "ventes")

 DELIMITER //
-- Crée un déclencheur BEFORE DELETE nommé "cancel_sale" sur la table "ventes"
CREATE TRIGGER cancel_sale BEFORE DELETE ON ventes
FOR EACH ROW
BEGIN
    -- Annule la vente en restaurant la quantité en stock du produit
    UPDATE produits
    SET quantiteStock = quantiteStock + OLD.QuantiteVendue
    WHERE id = OLD.idProduit;
END;
//
DELIMITER ;

Q Déclencheur AFTER INSERT pour ajouter la vente à un journal des ventes (table "ventes")

 DELIMITER //
-- Crée un déclencheur AFTER INSERT nommé "log_sale" sur la table "ventes"
CREATE TRIGGER log_sale AFTER INSERT ON ventes
FOR EACH ROW
BEGIN
    -- Insère une entrée dans un journal des ventes pour enregistrer la nouvelle vente
    INSERT INTO journal_des_ventes (vente_id, date_vente, montant_total)
    VALUES (NEW.idvente, NEW.dateVente, NEW.prixVente * NEW.QuantiteVendue);
END;
//
DELIMITER ;

Q Déclencheur AFTER DELETE pour enregistrer la suppression d'un client (table "clients")

 DELIMITER //
-- Crée un déclencheur AFTER DELETE nommé "log_deleted_client" sur la table "clients"
CREATE TRIGGER log_deleted_client AFTER DELETE ON clients
FOR EACH ROW
BEGIN
    -- Insère une entrée dans un journal pour enregistrer la suppression du client
    INSERT INTO journal_suppression_clients (client_id, date_suppression)
    VALUES (OLD.id, NOW());
END;
//
DELIMITER ;

Q Déclencheur BEFORE UPDATE pour empêcher la modification du nom du client (table "clients")

 DELIMITER //
-- Crée un déclencheur BEFORE UPDATE nommé "prevent_name_change" sur la table "clients"
CREATE TRIGGER prevent_name_change BEFORE UPDATE ON clients
FOR EACH ROW
BEGIN
    -- Vérifie si le nom du client a été modifié
    IF NEW.nom <> OLD.nom THEN
        -- Si le nom du client a été modifié, signale une erreur avec un message personnalisé
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Le nom du client ne peut pas être modifié.';
    END IF;
END;
//
DELIMITER ;

Q Déclencheur BEFORE INSERT pour vérifier la validité du client avant l'ajout d'une nouvelle vente (table "ventes")

 DELIMITER //
-- Crée un déclencheur BEFORE INSERT nommé "validate_customer" sur la table "ventes"
CREATE TRIGGER validate_customer BEFORE INSERT ON ventes
FOR EACH ROW
BEGIN
    -- Vérifie si le client existe dans la table "clients"
    IF NOT EXISTS (SELECT 1 FROM clients WHERE id = NEW.idClient) THEN
        -- Si le client n'existe pas, signale une erreur avec un message personnalisé
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Client inexistant. Veuillez ajouter le client d'abord.';
    END IF;
END;
//
DELIMITER ;

Q Déclencheur AFTER INSERT pour mettre à jour le montant total des ventes après l'ajout d'une nouvelle vente (table "ventes")

DELIMITER //
-- Crée un déclencheur AFTER INSERT nommé "update_total_sales_amount" sur la table "ventes"
CREATE TRIGGER update_total_sales_amount AFTER INSERT ON ventes
FOR EACH ROW
BEGIN
    -- Met à jour le montant total des ventes
    UPDATE total_ventes
    SET montant_total = montant_total + (NEW.prixVente * NEW.QuantiteVendue);
END;
//
DELIMITER ;

Q Déclencheur BEFORE DELETE pour vérifier si une vente peut être supprimée en fonction de règles spécifiques (table "ventes")

DELIMITER //
-- Crée un déclencheur BEFORE DELETE nommé "check_deletion_rules" sur la table "ventes"
CREATE TRIGGER check_deletion_rules BEFORE DELETE ON ventes
FOR EACH ROW
BEGIN
    -- Insérez ici les règles spécifiques pour la suppression des ventes
    -- Par exemple, vous pourriez vérifier si la vente est archivée ou non.
    -- Si les règles ne sont pas respectées, signalez une erreur.
END;
//
DELIMITER ;

Q Déclencheur BEFORE UPDATE pour limiter le changement de quantité vendue après la vente (table "ventes")

DELIMITER //
-- Crée un déclencheur BEFORE UPDATE nommé "limit_quantity_change" sur la table "ventes"
CREATE TRIGGER limit_quantity_change BEFORE UPDATE ON ventes
FOR EACH ROW
BEGIN
    -- Vérifie si la quantité vendue a été modifiée
    IF NEW.QuantiteVendue <> OLD.QuantiteVendue THEN
        -- Si la quantité vendue est modifiée, signale une erreur avec un message personnalisé
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La quantité vendue ne peut pas être modifiée après la vente.';
    END IF;
END;
//
DELIMITER ;

Q Déclencheur BEFORE INSERT pour vérifier la validité du produit avant l'ajout d'une nouvelle vente (table "ventes")

DELIMITER //
-- Crée un déclencheur BEFORE INSERT nommé "validate_product" sur la table "ventes"
CREATE TRIGGER validate_product BEFORE INSERT ON ventes
FOR EACH ROW
BEGIN
    -- Vérifie si le produit existe dans la table "produits"
    IF NOT EXISTS (SELECT 1 FROM produits WHERE id = NEW.idProduit) THEN
        -- Si le produit n'existe pas, signale une erreur avec un message personnalisé
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Produit inexistant. Veuillez ajouter le produit d'abord.';
    END IF;
END;
//
DELIMITER ;

Q Déclencheur BEFORE INSERT pour valider la catégorie du produit (table "produits")

DELIMITER //
-- Crée un déclencheur BEFORE INSERT nommé "validate_category" sur la table "produits"
CREATE TRIGGER validate_category BEFORE INSERT ON produits
FOR EACH ROW
BEGIN
    -- Vérifie si la catégorie du produit est valide
    IF NEW.categorie NOT IN ('Catégorie A', 'Catégorie B', 'Catégorie C') THEN
        -- Si la catégorie n'est pas valide, signale une erreur avec un message personnalisé
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Catégorie de produit non valide.';
    END IF;
END;
//
DELIMITER ;

Q Déclencheur AFTER INSERT pour ajouter un produit à un index de recherche (table "produits")

DELIMITER //
-- Crée un déclencheur AFTER INSERT nommé "add_to_search_index" sur la table "produits"
CREATE TRIGGER add_to_search_index AFTER INSERT ON produits
FOR EACH ROW
BEGIN
    -- Ajoute le produit à un index de recherche pour une recherche plus efficace
    INSERT INTO index_recherche_produits (produit_id, libelle, marque)
    VALUES (NEW.id, NEW.libelle, NEW.marque);
END;
//
DELIMITER ;

Q Déclencheur BEFORE INSERT pour valider l'âge du client en fonction de la date de naissance (table "clients")

DELIMITER //
-- Crée un déclencheur BEFORE INSERT nommé "validate_age" sur la table "clients"
CREATE TRIGGER validate_age BEFORE INSERT ON clients
FOR EACH ROW
BEGIN
    -- Calcule l'âge du client en fonction de la date de naissance
    SET @age = TIMESTAMPDIFF(YEAR, NEW.dateNaissance, CURDATE());
    
    -- Vérifie si l'âge du client est inférieur à 18 ans
    IF @age < 18 THEN
        -- Si l'âge est inférieur à 18 ans, signale une erreur avec un message personnalisé
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Le client doit avoir au moins 18 ans.';
    END IF;
END;
//
DELIMITER ;

Q Déclencheur BEFORE INSERT pour valider le nom du client en utilisant une expression régulière (table "clients")

DELIMITER //
-- Crée un déclencheur BEFORE INSERT nommé "validate_name" sur la table "clients"
CREATE TRIGGER validate_name BEFORE INSERT ON clients
FOR EACH ROW
BEGIN
    -- Vérifie que le nom du client suit un format spécifique (par exemple, première lettre en majuscule)
    IF NEW.nom NOT REGEXP '^[A-Z][a-z]+$' THEN
        -- Si le format du nom n'est pas respecté, signale une erreur avec un message personnalisé
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Le nom doit commencer par une majuscule, suivie de lettres minuscules.';
    END IF;
END;
//
DELIMITER ;

Q Déclencheur AFTER UPDATE pour mettre à jour les données du client dans un autre système (table "clients")

DELIMITER //
-- Crée un déclencheur AFTER UPDATE nommé "update_external_system" sur la table "clients"
CREATE TRIGGER update_external_system AFTER UPDATE ON clients
FOR EACH ROW
BEGIN
    -- Met à jour les données du client dans un autre système externe
    -- Cela pourrait impliquer une intégration avec un autre système ou une API externe.
    -- Insérez ici le code pour mettre à jour le système externe.
END;
//
DELIMITER ;

Q Déclencheur AFTER INSERT pour ajouter une référence de catégorie à la description du produit (table "produits")

DELIMITER //
-- Crée un déclencheur AFTER INSERT nommé "add_category_reference" sur la table "produits"
CREATE TRIGGER add_category_reference AFTER INSERT ON produits
FOR EACH ROW
BEGIN
    -- Ajoute une référence de catégorie à la description du produit
    SET NEW.libelle = CONCAT(NEW.libelle, ' (Catégorie : ', NEW.categorie, ')');
END;
//
DELIMITER ;

Q Déclencheur BEFORE INSERT pour vérifier la longueur de la description du produit (table "produits")

DELIMITER //
-- Crée un déclencheur BEFORE INSERT nommé "validate_description_length" sur la table "produits"
CREATE TRIGGER validate_description_length BEFORE INSERT ON produits
FOR EACH ROW
BEGIN
    -- Vérifie que la description du produit a une longueur maximale de 200 caractères
    IF LENGTH(NEW.libelle) > 200 THEN
        -- Si la description est trop longue, signale une erreur avec un message personnalisé
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La description du produit est trop longue (maximum 200 caractères).';
    END IF;
END;
//
DELIMITER ;

Q Déclencheur AFTER INSERT pour ajouter le produit à une liste de produits recommandés (table "produits")

DELIMITER //
-- Crée un déclencheur AFTER INSERT nommé "add_to_recommended_products" sur la table "produits"
CREATE TRIGGER add_to_recommended_products AFTER INSERT ON produits
FOR EACH ROW
BEGIN
    -- Ajoute le produit à une liste de produits recommandés
    INSERT INTO produits_recommandes (produit_id)
    VALUES (NEW.id);
END;
//
DELIMITER ;

Q Déclencheur BEFORE UPDATE pour empêcher la modification de la marque du produit (table "produits")

DELIMITER //
-- Crée un déclencheur BEFORE UPDATE nommé "prevent_brand_change" sur la table "produits"
CREATE TRIGGER prevent_brand_change BEFORE UPDATE ON produits
FOR EACH ROW
BEGIN
    -- Vérifie si la marque du produit a été modifiée
    IF NEW.marque <> OLD.marque THEN
        -- Si la marque a été modifiée, signale une erreur avec un message personnalisé
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La marque du produit ne peut pas être modifiée.';
    END IF;
END;
//
DELIMITER ;

Fonctions et procédures

Les triggers

la Sécurité de données

Exercices

Mysql

SELECT

DataTypes

UPDATE

DELETE

CREATE

DROP

RENAME

ALTER

Tables

Les déclencheurs (Trigger)