UPDATE Animal
SET disponible = FALSE
WHERE id = NEW.animal_id;
END IF;
END |
CREATE TRIGGER after_delete_adoption AFTER DELETE
ON Adoption FOR EACH ROW
BEGIN
UPDATE Animal
SET disponible = TRUE
WHERE id = OLD.animal_id;
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 |
CREATE TRIGGER before_insert_race BEFORE INSERT
ON Race FOR EACH ROW
BEGIN
SET NEW.date_insertion = NOW();
Partie 6 : Au-delà des tables classiques : vues, tables temporaires et vues matérialisées
331/414
www.openclassrooms.com