Les triggers(déclencheurs)

Avant propos: Les déclencheurs DDL, ne sont pas traités dans cet article.

Les triggers, ou déclencheurs, de base de données permettent d'associer un script Transact SQL à une action faite sur les données.

Les triggers vont permettre de mettre en place des contraintes d'intégrités complexes (comme par exemple empêcher un client de passer une nouvelle commande tant qu'il n'est pas à jour de ses règlements) et de maintenir des colonnes non normalisées (comme par exemple de répéter le nom d'un client sur la facture).

Les triggers sont toutefois plus long en terme de vérification qu'une contrainte d'intégrité. Il est donc souhaitable de ne les utiliser que lorsqu'il n'est pas possible d'utiliser les contraintes d'intégrités définies au niveau de la table. De plus les triggers sont exécuté lors de l'insertion, la modification ou bien la suppression d'une ligne d'information. Lorsqu'un nouveau trigger est défini sur une table rien ne permet de garantir que les informations déjà présentent dans la table respecte les contraintes mise en place à l'aide des triggers.

Les triggers sont exécuté après vérification des contraintes d'intégrités (AFTER). Les données nouvelles dans la table sont visibles par l'intermédiaire de la table inserted, et les informations qui ne sont plus présentent dans la table sont visibles par l'intermédiaire de la table deleted. Ces 2 tables (inserted et deleted) sont accessibles uniquement dans les triggers, car le trigger fait partie de l'instruction insert, update ou delete d'origine. Le tableau ci dessous présente la disponibilité des tables inserted et deleted en fonction des instructions SQL exécutés.

	INSERTED	DELETED
INSERT
UPDATE
DELETE

Définir un nouveau trigger

Un trigger correspond à un lot d'instruction Transact SQL associé à une instruction INSERT, UPDATE ou DELETE et une table. Il est possible de définir un nouveau trigger soit en écrivant un nouveau script Transact SQL, soit en utilisant l'interface graphique de SQL Server Management Studio pour appeler le modèle de scripts relatif à la création d'un nouveau trigger.

Quelque soit la solution retenue, la création d'un nouveau trigger respecte la syntaxe suivante:

CREATE TRIGGER nomTrigger
ON nomTable
AFTER {INSERT|DELETE|UPDATE}
AS
BEGIN
  SET NOCOUNT ON;
  -- La logique du trigger
END

Par exemple, un nouveau trigger va être défini pour associer automatiquement une catégorie à une adhésion en fonction de la date de naissance de l'individu.

CREATE TRIGGER Categorie
   ON  Adhesions 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	declare @annee int;
	declare @codeCategorie nchar(2);
    declare @numeroAdhesion int;
    declare @numeroIndividu int;
	declare lesAdhesions cursor for select numero, individu from inserted;
    -- ouvrir le curseur
	open lesAdhesions;
	fetch lesAdhesions into @numeroAdhesion, @numeroIndividu;
    while (@@FETCH_STATUS=0)
	begin
		-- Retrouver l'année de naissance de d'individu
		select @annee=datepart(year,nele) 
		from individus
		where numero=@numeroIndividu;
		-- Selectionner la catégorie en fonction de l'année
		select @codeCategorie=code
		from categories
		where @annee between anneefin and anneedebut
		-- Valoriser le champ manquant dans la table
		update Adhesions set categorie=@codeCategorie where numero=@numeroAdhesion
		-- Passer à l'enregistrement suivant
		fetch lesAdhesions into @numeroAdhesion, @numeroIndividu;
	end;
	-- fermer le curseur
	close lesAdhesions;
	deallocate lesAdhesions;
END;
			

Un second trigger est défini pour garantir qu'un individu ne peut pas être surclassé de plus d'une catégorie

CREATE TRIGGER dbo.Surclassement 
   ON  dbo.Adhesions 
   AFTER INSERT, UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    -- La logique du trigger
	declare curseurAdhesions cursor for select numero, individu, categorie from inserted;
	declare @numeroAdhesion int;
	declare @numeroIndividu int;
	declare @codeCategorie nchar(2);
	-- Parcourir toutes les nouvelles adhesions
	open curseurAdhesions;
	fetch curseurAdhesions into @numeroAdhesion,@numeroIndividu,@codeCategorie;
	while (@@FETCH_STATUS=0)
    begin
		-- lire la catégorie associée à l'adhésion
		if (@codeCategorie is not null)
		begin
			declare @categorieNormale nchar(2);
			declare @annee int;
			-- Retrouver l'année de naissance de d'individu
			select @annee=datepart(year,nele) 
			from individus
			where numero=@numeroIndividu;
			-- Selectionner la catégorie en fonction de l'année
			select @categorieNormale=code
			from categories
			where @annee between anneefin and anneedebut
			-- Y a t il surclassement?
			if (@categorieNormale!=@codeCategorie)
			begin
				-- traite le cas spécial des Vétérans
				if  (@categorieNormale='VE' AND @codeCategorie!='SE')
				begin
					RAISERROR ('Surclassement impossible',10,1);
				end;
				-- traiter les autres cas
				if (@categorieNormale!='VE')
				begin
					declare @categorieSurclassement nchar(2);
					declare laCategorieSurclassement cursor for 
						select code 
						from categories 
						where anneedebut < @annee 
						order by anneefin DESC.
					open laCategorieSurclassement;
					fetch laCategorieSurclassement into @categorieSurclassement;
					if (@@FETCH_STATUS=0)
					begin
						if (@categorieSurclassement!=@codeCategorie)
						begin
							RAISERROR ('Surclassement impossible',10,1);
						end;
					end;
					close laCategorieSurclassement;
					deallocate laCategorieSurclassement;
				end;
			end;
		end;
		
		-- Passer à l'enregistrement suivant
		fetch curseurAdhesions into @numeroAdhesion,@numeroIndividu,@codeCategorie;
	end;
	close curseurAdhesions;
	deallocate curseurAdhesions;
END;
			

Définir plusieurs triggers pour une même table et une même action

Il est possible de définir plusieurs triggers pour une table et une action données. La procédure sp_settriggerorder permet de fixer le premier et le dernier déclencheurs à exécuter. Pour les autres triggers, il n'est pas possible de fixer l'ordre d'exécution.

sp_settriggerorder [ @triggername = ] 'nomTrigger' ,
[ @order = ] {'FIRST','LAST','NONE',} ,
[ @stmttype = ] {'INSERT','UPDATE','DELETE',...}
[ , [ @namespace = ] { 'DATABASE' | 'SERVER' | NULL } ]

Dans l'exemple suivant l'ordre des déclencheurs est défini par rapport à l'instruction INSERT sur la table des Adhesions.

Remarque: l'option namespace n'est à spécifier que pour les déclencheurs DLL.

Définir un trigger sur une vue

Il est possible de définir des déclencheurs sur une vue. Dans ce cas les déclencheurs vont "capturer" l'instruction INSERT, UPDATE ou DELETE initiale pour construire une ou plusieurs instructions INSERT, UPDATE ou DELETE sur les tables associées à la vue.

p> CREATE TRIGGER nomTrigger
ON nomVue
INSTEAD OF {INSERT|DELETE|UPDATE}
AS
BEGIN
  SET NOCOUNT ON;
  -- La logique du trigger
END