ALTER TABLE Adoption ADD CONSTRAINT fk_client_id FOREIGN KEY (client_id) REFERENCES
ALTER TABLE VM_Revenus_annee_espece ADD CONSTRAINT fk_vm_revenu_espece_id FOREIGN
DELIMITER |
CREATE TRIGGER before_insert_adoption BEFORE INSERT
ON Adoption FOR EACH ROW
BEGIN
IF NEW.paye != TRUE
AND NEW.paye != FALSE
THEN
INSERT INTO Erreur (erreur) VALUES ('Erreur : paye doit valoir TRUE (1) ou F
ELSEIF NEW.date_adoption < NEW.date_reservation THEN
INSERT INTO Erreur (erreur) VALUES ('Erreur : date_adoption doit être >= à d
END IF;
END |
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 |
CREATE TRIGGER before_update_adoption BEFORE UPDATE
ON Adoption FOR EACH ROW
BEGIN
IF NEW.paye != TRUE
AND NEW.paye != FALSE
THEN
INSERT INTO Erreur (erreur) VALUES ('Erreur : paye doit valoir TRUE (1) ou F
ELSEIF NEW.date_adoption < NEW.date_reservation THEN
INSERT INTO Erreur (erreur) VALUES ('Erreur : date_adoption doit être >= à d
END IF;
END |
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);
Partie 7 : Gestion des utilisateurs et configuration du serveur
378/414
www.openclassrooms.com