Déclencheurs imbriqués
Les déclencheurs DML et DDL sont imbriqués lorsqu'un déclencheur exécute une action qui lance un autre déclencheur. Ces actions peuvent lancer d'autres déclencheurs, et ainsi de suite. Les déclencheurs DML et DDL peuvent avoir jusqu'à 32 niveaux d'imbrication. Une référence au code managé à partir d'un déclencheur Transact-SQL compte pour un niveau quant à la limite d'imbrication de 32 niveaux. Les méthodes appelées depuis du code managé n'entrent pas en compte dans cette limite.
Vous pouvez définir si les déclencheurs AFTER peuvent être imbriqués à l'aide de l'option de configuration serveur Déclencheurs imbriqués. Les déclencheurs INSTEAD OF (seuls les déclencheurs DML peuvent être des déclencheurs INSTEAD OF) peuvent être imbriqués quelle que soit la valeur de ce paramètre.
[!REMARQUE]
Dans SQL Server 2000, aucun déclencheur AFTER imbriqué dans un déclencheur INSTEAD OF ne s'exécute lorsque l'option de configuration du serveur de nested triggers est désactivée (OFF). Dans SQL Server 2005 ou version ultérieure, le premier déclencheur AFTER imbriqué dans un déclencheur INSTEAD OF se déclenche même si l'option de configuration du serveur de nested triggers est définie à 0. Toutefois, les déclencheurs AFTER suivants ne se déclenchent pas avec ce paramètre. Contrôlez les déclencheurs imbriqués de vos applications afin de déterminer si ces applications sont toujours conformes aux règles d'entreprise relatives à ce nouveau comportement lorsque l'option de configuration du serveur de nested triggers est définie à 0, puis effectuez les modifications nécessaires.
Si l'imbrication est permise et qu'un déclencheur de la chaîne démarre une boucle infinie, le niveau d'imbrication maximal est dépassé et le déclencheur s'arrête.
Les déclencheurs imbriqués peuvent s'avérer utiles pour effectuer certaines fonctions de maintenance, comme stocker une copie de sauvegarde des lignes concernées par un déclencheur précédent. Par exemple, vous pouvez créer un déclencheur sur PurchaseOrderDetail, qui enregistre une copie de sauvegarde des lignes PurchaseOrderDetail qui ont été supprimées par le déclencheur delcascadetrig. Lorsque le déclencheur delcascadetrig est actif, la suppression de PurchaseOrderID 1965 de PurchaseOrderHeader supprime la ou les lignes correspondantes de PurchaseOrderDetail. Pour enregistrer les données, vous pouvez créer un déclencheur DELETE sur PurchaseOrderDetail, qui enregistre les données supprimées dans une autre table del_save créée séparément. Par exemple :
CREATE TRIGGER savedel
ON Purchasing.PurchaseOrderDetail
FOR DELETE
AS
INSERT del_save
SELECT * FROM deleted
Il n'est pas recommandé d'utiliser des déclencheurs imbriqués dans une séquence où l'ordre a de l'importance. Employez des déclencheurs séparés pour effectuer des modifications de données en cascade.
[!REMARQUE]
Comme les déclencheurs s'exécutent au sein d'une transaction, un échec à un quelconque niveau d'un ensemble de déclencheurs imbriqués annule la transaction tout entière, entraînant la restauration de toutes les modifications de données. Pour déterminer l'emplacement où l'erreur a eu lieu, incluez dans vos déclencheurs des instructions PRINT.
Déclencheurs récursifs
Un déclencheur AFTER ne s'appelle pas lui-même de manière récursive, à moins que l'option de base de données RECURSIVE_TRIGGERS ne soit définie.
Il existe deux types d'autorisations :
la récursion directe ;
Elle se produit lorsqu'un déclencheur est activé et exécute une action qui l'active de nouveau. Par exemple, une application met à jour la table T3, ce qui active le déclencheur Trig3. Trig3 met à jour de nouveau la table T3, ce qui active encore le déclencheur Trig3.
Dans SQL Server 2008, la récurrence directe peut également se produire si le même déclencheur est rappelé, mais après l'appel d'un type différent (AFTER ou INSTEAD OF) de déclencheur. Autrement dit, la récurrence directe d'un déclencheur INSTEAD OF peut se produire lorsque le même déclencheur est rappelé, même si un ou plusieurs déclencheurs AFTER ont été appelés entre temps. De même, la récurrence directe d'un déclencheur AFTER peut se produire lorsque le même déclencheur est rappelé, même si un ou plusieurs déclencheurs INSTEAD OF ont été appelés entre temps. Par exemple, une application met à jour la table T4. Cette mise à jour entraîne l'activation du déclencheur INSTEAD OF Trig4. Trig4 met à jour la table T5. Cette mise à jour entraîne l'activation du déclencheur AFTER Trig5. Trig5 met à jour la table T4, et cette mise à jour entraîne de nouveau l'activation du déclencheur INSTEAD OF Trig4. Cette chaîne d'événements est une récursion directe pour Trig4.
la récursion indirecte ;
Elle se produit lorsqu'un déclencheur est activé et exécute une action qui active un autre déclencheur du même type (AFTER ou INSTEAD OF). Le deuxième déclencheur exécute une action qui active de nouveau le déclencheur de départ. En d'autres termes, la récursion indirecte peut se produire lorsqu'un déclencheur INSTEAD OF est rappelé, mais pas avant l'appel d'un autre déclencheur INSTEAD OF entre temps. De même, la récursion indirecte peut se produire lorsqu'un déclencheur AFTER est rappelé, mais pas avant l'appel d'un autre déclencheur AFTER entre temps. Par exemple, une application met à jour la table T1. Cette mise à jour entraîne l'activation du déclencheur AFTER Trig1. Trig1 met à jour la table T2, ce qui active le déclencheur AFTER Trig2. Trig2 à son tour met à jour la table T1, ce qui active de nouveau le déclencheur AFTER Trig1.
Seule la récurrence directe des déclencheurs AFTER est neutralisée lorsque l'option de base de données RECURSIVE_TRIGGERS est désactivée (OFF). Pour désactiver la récurrence indirecte des déclencheurs AFTER, affectez en plus la valeur 0 à l'option de serveur nested triggers.
Exemples
L'exemple suivant illustre l'utilisation des déclencheurs récursifs pour résoudre une relation faisant référence à elle-même, également appelé fermeture transitive. Par exemple, la table emp_mgr définit les éléments suivants :
un salarié (emp) dans une entreprise ;
le responsable de chaque salarié (mgr) ;
le nombre total de salariés dans l'arborescence de l'organisation rattachés à chaque salarié (NoOfReports).
Un déclencheur de mise à jour récursif peut être utilisé pour maintenir à jour la colonne NoOfReports au fur et à mesure de l'insertion de l'enregistrement de nouveaux salariés. Le déclencheur INSERT met à jour la colonne NoOfReports de l'enregistrement du responsable, provoquant la mise à jour récursive de la colonne NoOfReports des autres enregistrements en remontant la hiérarchie.
USE AdventureWorks;
GO
-- Turn recursive triggers ON in the database.
ALTER DATABASE AdventureWorks
SET RECURSIVE_TRIGGERS ON;
GO
CREATE TABLE emp_mgr (
emp char(30) PRIMARY KEY,
mgr char(30) NULL FOREIGN KEY REFERENCES emp_mgr(emp),
NoOfReports int DEFAULT 0
);
GO
CREATE TRIGGER emp_mgrins ON emp_mgr
FOR INSERT
AS
DECLARE @e char(30), @m char(30)
DECLARE c1 CURSOR FOR
SELECT emp_mgr.emp
FROM emp_mgr, inserted
WHERE emp_mgr.emp = inserted.mgr
OPEN c1
FETCH NEXT FROM c1 INTO @e
WHILE @@fetch_status = 0
BEGIN
UPDATE emp_mgr
SET emp_mgr.NoOfReports = emp_mgr.NoOfReports + 1 -- Add 1 for newly
WHERE emp_mgr.emp = @e -- added employee.
FETCH NEXT FROM c1 INTO @e
END
CLOSE c1
DEALLOCATE c1
GO
-- This recursive UPDATE trigger works assuming:
-- 1. Only singleton updates on emp_mgr.
-- 2. No inserts in the middle of the org tree.
CREATE TRIGGER emp_mgrupd ON emp_mgr FOR UPDATE
AS
IF UPDATE (mgr)
BEGIN
UPDATE emp_mgr
SET emp_mgr.NoOfReports = emp_mgr.NoOfReports + 1 -- Increment mgr's
FROM inserted -- (no. of reports) by
WHERE emp_mgr.emp = inserted.mgr -- 1 for the new report.
UPDATE emp_mgr
SET emp_mgr.NoOfReports = emp_mgr.NoOfReports - 1 -- Decrement mgr's
FROM deleted -- (no. of reports) by 1
WHERE emp_mgr.emp = deleted.mgr -- for the new report.
END
GO
-- Insert some test data rows.
INSERT emp_mgr(emp, mgr) VALUES ('Harry', NULL)
INSERT emp_mgr(emp, mgr) VALUES ('Alice', 'Harry')
INSERT emp_mgr(emp, mgr) VALUES ('Paul', 'Alice')
INSERT emp_mgr(emp, mgr) VALUES ('Joe', 'Alice')
INSERT emp_mgr(emp, mgr) VALUES ('Dave', 'Joe')
GO
SELECT * FROM emp_mgr
GO
-- Change Dave's manager from Joe to Harry
UPDATE emp_mgr SET mgr = 'Harry'
WHERE emp = 'Dave';
GO
SELECT * FROM emp_mgr;
GO
Résultats avant la mise à jour :
emp mgr NoOfReports
------------------------------ ----------------------------- -----------
Alice Harry 2
Dave Joe 0
Harry NULL 1
Joe Alice 1
Paul Alice 0
Résultats après la mise à jour :
emp mgr NoOfReports
------------------------------ ----------------------------- -----------
Alice Harry 2
Dave Harry 0
Harry NULL 2
Joe Alice 0
Paul Alice 0
Pour définir l'option nested triggers
Pour définir l'option RECURSIVE_TRIGGERS de base de données
Voir aussi