CREATE TRIGGER ProduitAlerteStock AFTER UPDATE ON tblproduit
FOR EACH ROW
    BEGIN    
    DECLARE nAlerte1 varchar(200);
    DECLARE nAlerte2 varchar(200);
    DECLARE nAlerte3 varchar(200);
    SET nAlerte1 = (SELECT Description FROM tblNiveauAlerte WHERE Reference="LEVEL-1");
    SET nAlerte2 = (SELECT Description FROM tblNiveauAlerte WHERE Reference="LEVEL-2");
    SET nAlerte3 = (SELECT Description FROM tblNiveauAlerte WHERE Reference="LEVEL-3");
    
    IF (NEW.Quantite <= NEW.Qte_Min AND NEW.Quantite > NEW.Qte_Critique) THEN
        INSERT INTO tblAlerteStock 
        (TypeAlerte, RefProduit, Designation)
        VALUES 
        (@nAlerte1, NEW.Reference, NEW.Designation);
    ELSEIF (NEW.Quantite = NEW.Qte_Critique) THEN
        UPDATE tblAlerteStock (TypeAlerte)
        SET (@nAlerte2)
        WHERE RefProduit = NEW.Reference;
    ELSEIF (NEW.Quantite < NEW.Qte_Critique) THEN
        UPDATE tblAlerteStock (TypeAlerte)
        SET (@nAlerte3)
        WHERE RefProduit = NEW.Reference;
    END IF;
END;%