CREATE TRIGGER before_insert_race BEFORE INSERT
ON Race FOR EACH ROW
BEGIN
SET NEW.date_insertion = NOW();
SET NEW.utilisateur_insertion = CURRENT_USER();
SET NEW.date_modification = NOW();
SET NEW.utilisateur_modification = CURRENT_USER();
END |
CREATE TRIGGER before_update_race BEFORE UPDATE
ON Race FOR EACH ROW
BEGIN
SET NEW.date_modification = NOW();
SET NEW.utilisateur_modification = CURRENT_USER();
END |
CREATE TRIGGER before_delete_race BEFORE DELETE
ON Race FOR EACH ROW
BEGIN
UPDATE Animal
SET race_id = NULL
WHERE race_id = OLD.id;
END |
DELIMITER ;
CREATE VIEW V_Animal_details AS
select Animal.id,Animal.sexe,Animal.date_naissance,Animal.nom,Animal.commentaires
Animal.mere_id,Animal.pere_id,Animal.disponible,Espece.nom_courant AS espece_nom
from Animal
join Espece on Animal.espece_id = Espece.id
left join Race on Animal.race_id = Race.id;
CREATE VIEW V_Animal_espece AS
select Animal.id,Animal.sexe,Animal.date_naissance,Animal.nom,Animal.commentaires
Animal.mere_id,Animal.pere_id,Animal.disponible,Espece.nom_courant AS espece_nom
from Animal
join Espece on Espece.id = Animal.espece_id;
CREATE VIEW V_Animal_stagiaire AS
select Animal.id,Animal.nom,Animal.sexe,Animal.date_naissance,Animal.espece_id,
from Animal
where Animal.espece_id = 2
WITH CASCADED CHECK OPTION;
CREATE VIEW V_Chien AS
select Animal.id,Animal.sexe,Animal.date_naissance,Animal.nom,Animal.commentaires
from Animal
where Animal.espece_id = 1;
CREATE VIEW V_Chien_race AS
select
V_Chien.id,V_Chien.sexe,V_Chien.date_naissance,V_Chien.nom,V_Chien.commentaires
from V_Chien
where V_Chien.race_id is not null
WITH CASCADED CHECK OPTION;
CREATE VIEW V_Client AS
select Client.id,Client.nom,Client.prenom,Client.adresse,Client.code_postal,Client
from Client;
CREATE VIEW V_Espece_dollars AS
select Espece.id,Espece.nom_courant,Espece.nom_latin,Espece.description,round((
from Espece;
CREATE VIEW V_Nombre_espece AS
select Espece.id,count(Animal.id) AS nb
from Espece
left join Animal on Animal.espece_id = Espece.id
Partie 7 : Gestion des utilisateurs et configuration du serveur
380/414
www.openclassrooms.com