Suivant les expériences et sensibilités de chacun, les Déclencheurs (Triggers en anglais) sont acceptés et utilisés par certains, ou fuis comme la peste par d’autres.
Outre l’architecture technique permettant parfois d’exclure les codages sous forme de déclencheurs, il est aussi indispensable de bien comprendre le principe de fonctionnement des déclencheurs afin de ne pas laisser traîner des bugs qui seront très difficiles à corriger.

Nous ne nous intéresserons ici qu’aux déclencheurs de type DML (Data Modification Language), car c’est ce type de déclencheurs qui peut le plus souvent être mal maîtrisé et au final devenir contre-productif.

Premièrement, qu’est-ce qu’un déclencheur ? Et bien c’est un bloc de code qui s’exécute automatiquement, de manière totalement implicite, après ou à la place d’une instruction de modification (INSERT, DELETE ou UPDATE) dans une table donnée. Ce déclencheur s’exécutera automatiquement pour chaque instruction concernée. Prenons par exemple le code suivant (je m’appuierai ici sur les bases d’exemple issues de http://msftdbprodsamples.codeplex.com/) :

CREATE TRIGGER Sales.trigCurrency
   ON  Sales.Currency
   AFTER INSERT
AS
BEGIN
	DECLARE @name nvarchar(50)
	SELECT @name = Name
	FROM inserted
	IF len(@name) < 5
	BEGIN
		ROLLBACK TRANSACTION
	END
END

Ce déclencheur s’exécute automatiquement lors de chaque instruction INSERT sur la table Sales.Currency. On remarquera donc que si les échanges entre le client et le serveur SQL se fait sous forme CRUD (Create / Read / Update / Delete), avec des instructions unitaires, le coût d’exécution du déclencheur peut être non négligeable. Ainsi, si 20 instructions INSERT sont exécutées pour définir 20 nouvelles devises, le déclencheur sera exécuté 20 fois.

set nocount on
declare @compteur int=0
while @compteur<20
begin
	insert into Sales.Currency (CurrencyCode,Name,ModifiedDate)
		select N'X'+convert(nvarchar(2),@compteur),
				N'DEVISE'+convert(nvarchar(2),@compteur),
				getdate()
	select @compteur+=1
end

Et c’est justement dans une mauvaise compréhension de ce mode de déclenchement que résident souvent quelques bugs.
Dans le cas présent, lors de l’insertion d’un nouvel enregistrement dans la table Sales.Currency, la valeur de la colonne Name est stockée dans une variable @Name, dont la longueur est ensuite déterminée, et si cette longueur est inférieure à 5 alors le système signale une erreur et l’insertion est refusée.

Mais que se passe-t-il lorsque plusieurs enregistrements sont insérés en une seule instruction ?

On remarque ici qu’un enregistrement incorrect s’est glissé au milieu du lot et n’a pas été intercepté par le déclencheur. La raison en est simple : le code du déclencheur a été exécuté une et une seule fois lors de l’instruction INSERT (et non pas une fois par ligne insérée). La valeur de la variable @Name n’a donc pas spécialement été maîtrisée (en fait, c’est la valeur de la première ligne qui a été prise en compte). Et au final, le déclencheur semble de pas avoir fait son travail.
En fait, il ne faut pas oublier ce que représente « inserted » dans le déclencheur. Il s’agit d’une table contenant les valeurs insérées, avec les colonnes correspondant à la table Sales.Currency, mais surtout avec autant de lignes qu’il se doit pour décrire l’ensemble de l’impact de l’instruction INSERT, et il ne faut surtout pas oublier que l’instruction INSERT permet tout à fait d’insérer plusieurs lignes d’un coup. Il s’agit donc de modifier le code du trigger pour prendre en compte cet état de fait, et faire en sorte que le déclencheur joue son rôle non seulement pour les insertions unitaires, mais aussi pour les insertions multiples.

ALTER TRIGGER Sales.trigCurrency
   ON  Sales.Currency
   AFTER INSERT
AS
BEGIN
	IF EXISTS (select *
				from inserted
				where len(Name) < 5)
	BEGIN
		ROLLBACK TRANSACTION
	END
END

Et cette fois-ci, plus moyen de cacher une valeur incorrecte au milieu d’un lot de bonnes données !

D’une manière générale, on peut dire qu’à partir du moment où l’on se met à déclarer des variables dans un déclencheur, il y a de fortes chances que l’on ait implémenté un code qui gère les données de manière unitaire et oublie totalement le fait que les instructions SQL peuvent manipuler plusieurs enregistrements d’un coup. Donc ne fuyez pas forcément les déclencheurs, mais soyez vigilants dans leur implémentation…