DELETE FROM VM_Revenus_annee_espece
WHERE nb = 0;
END |
CREATE TRIGGER after_delete_adoption AFTER DELETE
ON Adoption FOR EACH ROW
BEGIN
UPDATE Animal
SET disponible = TRUE
WHERE id = OLD.animal_id;
UPDATE VM_Revenus_annee_espece
SET somme = somme - OLD.prix, nb = nb - 1
WHERE annee = YEAR(OLD.date_reservation)
AND espece_id = (SELECT espece_id FROM Animal WHERE id = OLD.animal_id);
DELETE FROM VM_Revenus_annee_espece
WHERE nb = 0;
END |
CREATE TRIGGER before_insert_animal BEFORE INSERT
ON Animal FOR EACH ROW
BEGIN
IF NEW.sexe IS NOT NULL
AND NEW.sexe != 'M'
AND NEW.sexe != 'F'
THEN
INSERT INTO Erreur (erreur) VALUES ('Erreur : sexe doit valoir "M", "F" ou N
END IF;
END |
CREATE TRIGGER before_update_animal BEFORE UPDATE
ON Animal FOR EACH ROW
BEGIN
IF NEW.sexe IS NOT NULL
AND NEW.sexe != 'M'
AND NEW.sexe != 'F'
THEN
SET NEW.sexe = NULL;
END IF;
END |
CREATE TRIGGER after_update_animal AFTER UPDATE
ON Animal FOR EACH ROW
BEGIN
INSERT INTO Animal_histo (
id, sexe, date_naissance, nom, commentaires, espece_id, race_id,
mere_id, pere_id, disponible, date_histo, utilisateur_histo, evenement_histo
VALUES (
OLD.id, OLD.sexe, OLD.date_naissance, OLD.nom, OLD.commentaires, OLD.espece_
OLD.mere_id, OLD.pere_id, OLD.disponible, NOW(), CURRENT_USER(), 'UPDATE'
END |
CREATE TRIGGER after_delete_animal AFTER DELETE
ON Animal FOR EACH ROW
BEGIN
INSERT INTO Animal_histo (
id, sexe, date_naissance, nom, commentaires, espece_id, race_id,
mere_id, pere_id, disponible, date_histo, utilisateur_histo, evenement_hist
VALUES (
OLD.id, OLD.sexe, OLD.date_naissance, OLD.nom, OLD.commentaires, OLD.espece_
OLD.mere_id, OLD.pere_id, OLD.disponible, NOW(), CURRENT_USER(), 'DELETE'
END |
CREATE TRIGGER before_delete_espece BEFORE DELETE
ON Espece FOR EACH ROW
BEGIN
DELETE FROM Race
WHERE espece_id = OLD.id;
END |
Partie 7 : Gestion des utilisateurs et configuration du serveur
379/414
www.openclassrooms.com