-- Remplissage de la colonne
UPDATE Animal
SET disponible = FALSE
WHERE EXISTS (
SELECT *
FROM Adoption
WHERE Animal.id = Adoption.animal_id
);
-- Création des trois triggers
DELIMITER |
CREATE TRIGGER after_insert_adoption AFTER INSERT
ON Adoption FOR EACH ROW
BEGIN
UPDATE Animal
SET disponible = FALSE
WHERE id = NEW.animal_id;
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 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;
END |
DELIMITER ;
Test :
Code : SQL
SELECT animal_id, nom, sexe, disponible, client_id
FROM Animal
INNER JOIN Adoption ON Adoption.animal_id = Animal.id
WHERE client_id = 9;
animal_id nom sexe disponible client_id
33
Caribou M
0
9
54
Bubulle M
0
9
55
Relou
M
0
9
Code : SQL
DELETE FROM Adoption
Partie 5 : Sécuriser et automatiser ses actions
319/414
www.openclassrooms.com