Les triggers ou declencheurs | Cours SQL | Programmation SQL | Exercices Corrigés Trigger declencheur

I- DEFINITON

Un déclencheur (trigger) est un type particulier de procédures stockées qui s’exécute automatiquement lorsque vous modifiez, ajoutez ou supprimez des données d’une table.

Les triggers garantissent la cohérence des données liées dans différentes tables : par exemple, un trigger permettra de mettre à jour le NumAvion dans toutes les lignes de la table Affectation liées à un avion que l’on va supprimer dans la table Avion. 

Chaque trigger est spécifique à certaines opérations sur les données : update, insert ou delete. Il s’exécute immédiatement après l’opération qui l’a lancé. Le trigger et l’opération forment une seule transaction qui peut annulée par le trigger. Si une erreur grave est détectée, toute la transaction est automatiquement annulée.

En langage de base de données une transaction désigne une suite indivisible d’instructions SQL qui s’exécutent toutes ou aucune.

II-        Création d’un Trigger

1-   A l’aide d’Entreprise Manager

·        Cliquez sur le nom de la table

·        Modifier la table

·        Cliquez sur le bouton "déclencheurs".

·        Ecrire (modifier le trigger)

·        Ok

Syntaxe :

CREATE TRIGGER Nom_Trigger ON Nom_Table

FOR INSERT, UPDATE, DELETE

AS

Begin

  Instructions T-Sql

End

1-   2- A l’aide de l’analyseur de requêtes

Ecrire directement le trigger.

Exemples :

Ex1 :

Ecrire un trigger qui vérifie qu’un vol est effectué entre 2 aéroports différents.

create trigger verifie_aero on vol

for insert, update

as

Begin

declare @a1 varchar, @a2 varchar

select @a1=AeroportDept, @a2=AeroportArr from inserted

if @a1=@a2

 begin

   raiserror('l''aeroport de départ doit être différent de l''aeroport d''arrivée',16,1)

   ROLLBACK   -- Annuler la transaction

 end

End

Remarques:

·       INSERTED : est une table temporaire ayant la même structure que la table de base qui contient l’enregistrement à ajouter.

·       DELETED : est une table temporaire ayant la même structure que la table de base qui contient l’enregistrement à supprimer.

·       Au cours d’une modification d’un enregistrement :

-      DELETED contient l’enregistrement avant modification.

-      INSERTED contient l’enregistrement après modification.

Exercices

Soit la base de données Compagnie Aérienne

ÄExercice 1 : sécurisation des suppressions, avec modification en cascade

Dans la table PILOTE, créer un trigger en suppression, qui met à NULL tous les champs IdPilote  de la table AFFECTATION, liés au pilote à supprimer : avec cette méthode, on connaît les vols sans affectation, qui devront être affectés à un autre pilote. Tester le trigger en supprimant un pilote, et en réaffichant la table AFFECTATION.

Les lignes à supprimer sont stockées dans la table temporaire deleted. Pour cet exercice, il faut  supprimer la contrainte de clé étrangère sur la colonne IdPilote de la table AFFECTATION : comme les contraintes de clé étrangère sont évaluées avant le lancement des triggers, le lien entre AFFECTATION et PILOTE interdit la suppression des pilotes référencés dans la table AFFECTATION.

 create trigger supp_pilote on Pilote

  for delete

  as

  begin

  declare @idpilote int

  select @idpilote=idpilote from deleted

  update affectation

  set idpilote=NULL

  where idpilote=@idpilote

 end

ÄExercice 2 : test de cohérence en insertion sur la table VOL

Dans la table VOL, créer un trigger en insertion qui vérifie que la ville d’arrivée est différente de la ville de départ, et que l’heure d’arrivée est postérieur à l’heure de départ. Dans le cas contraire, on annule l’insertion en appelant l’instruction ROLLBACK.

create trigger t7 on Vol for insert

as

begin

  declare @hd int,@ha int,@vd varchar(3),@va varchar(3)

  select @hd=HDepart,@ha=HArrivée,@vd=AeroportDept,@va=AeroportArr

  from inserted

if(@hd>=@ha)

  begin

   raiserror('Erreur! Heure de depart doit être inférieure à l’heure d’arrivée',16,2)

   rollback transaction

return

end

if(@va=@vd)

  begin

   raiserror('Erreur! Aeroport de depart doit être different de l’aeroport d’arrivée',16,2)

   rollback transaction

end

end

ÄExercice 3 : test de cohérence en insertion sur la table AFFECTATION

Interdire à un pilote de voler plus de 35 heures par semaine, par un Trigger en insertion sur la table AFFECTATION.

La somme des heures de vol du pilote pour une semaine représentée par son numéro, est donnée par la procédure Horaire. Cette procédure ne prend pas en compte la nouvelle affectation, qui n’est pas encore dans la table AFFECTATION, mais dans la table provisoire Inserted

create trigger t8 on Affectation for insert

as

begin

declare @id int,@code int

declare @nom varchar(20),@prenom varchar(20),@ns int,@nh int

select @id=IdPilote,@ns=datename(week,DateVol) from inserted

select @nom=NomPilote,@prenom=PrenomPilote

from Pilote where IdPilote=@id

exec Horaire @nom,@prenom,@ns,@code output,@nh output

if(@nh>35)

begin

  raisError('Impossible d affecter à ce pilote encore de vols',16,9)

  rollback transaction

return

end

end