ADD CONSTRAINT fk_vm_revenu_espece_id FOREIGN KEY (espece_id)
REFERENCES Espece (id) ON DELETE CASCADE,
ADD PRIMARY KEY (annee, espece_id);
Et voici donc les triggers nécessaires à la mise à jour de la vue matérialisée :
Ces trois triggers existaient déjà, et permettaient de mettre à jour la colonne Animal.disponible. Il faut donc d'abord les
supprimer, puis les recréer en ajoutant la mise à jour de VM_Revenus_annee_espece.
Code : SQL
DELIMITER |
DROP TRIGGER after_insert_adoption |
CREATE TRIGGER after_insert_adoption AFTER INSERT
ON Adoption FOR EACH ROW
BEGIN
UPDATE Animal
SET disponible = FALSE
WHERE id = NEW.animal_id;
INSERT INTO VM_Revenus_annee_espece (espece_id, annee, somme,
nb)
SELECT espece_id, YEAR(NEW.date_reservation), NEW.prix, 1
FROM Animal
WHERE id = NEW.animal_id
ON DUPLICATE KEY UPDATE somme = somme + NEW.prix, nb = nb + 1;
END |
DROP TRIGGER after_update_adoption |
CREATE TRIGGER after_update_adoption AFTER UPDATE
ON Adoption FOR EACH ROW
BEGIN
IF OLD.animal_id <> NEW.animal_id THEN
UPDATE Animal
SET disponible = TRUE
WHERE id = OLD.animal_id;
UPDATE Animal
SET disponible = FALSE
WHERE id = NEW.animal_id;
END IF;
INSERT INTO VM_Revenus_annee_espece (espece_id, annee, somme,
nb)
SELECT espece_id, YEAR(NEW.date_reservation), NEW.prix, 1
FROM Animal
WHERE id = NEW.animal_id
ON DUPLICATE KEY UPDATE somme = somme + NEW.prix, nb = nb + 1;
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 |
DROP TRIGGER after_delete_adoption |
CREATE TRIGGER after_delete_adoption AFTER DELETE
ON Adoption FOR EACH ROW
BEGIN
UPDATE Animal
Partie 6 : Au-delà des tables classiques : vues, tables temporaires et vues matérialisées
369/414
www.openclassrooms.com