Partager via

Créer des déclencheurs imbriqués

Les déclencheurs DML et DDL sont imbriqués lorsqu’un déclencheur effectue 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 être imbriqués jusqu’à 32 niveaux. Vous pouvez contrôler dans quelle mesure les déclencheurs AFTER peuvent être imbriqués via l’option de configuration du 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 indépendamment de ce paramètre.

Remarque

Toute référence au code managé à partir d’un déclencheur Transact-SQL compte comme un niveau par rapport à la limite d’imbrication de 32 niveaux. Les méthodes appelées à partir du code managé ne comptent pas sur cette limite.

Si les déclencheurs imbriqués sont autorisés et qu’un déclencheur dans la chaîne démarre une boucle infinie, le niveau d’imbrication est dépassé et le déclencheur se termine.

Vous pouvez utiliser des déclencheurs imbriqués pour effectuer des fonctions de nettoyage utiles, telles que le stockage d’une copie de sauvegarde des lignes affecté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 que le déclencheur delcascadetrig a supprimées. Avec le déclencheur delcascadetrig en vigueur, supprimer PurchaseOrderID 1965 à partir de PurchaseOrderHeader supprime la ou les lignes correspondantes dans PurchaseOrderDetail. Pour enregistrer les données, vous pouvez créer un déclencheur DELETE sur PurchaseOrderDetail lequel les données supprimées sont enregistrées dans une autre table créée séparément. del_save Par exemple:

CREATE TRIGGER Purchasing.savedel  
   ON Purchasing.PurchaseOrderDetail  
FOR DELETE  
AS  
   INSERT del_save;  
   SELECT * FROM deleted;

Nous vous déconseillons d’utiliser des déclencheurs imbriqués dans une séquence dépendante de l’ordre. Utilisez des déclencheurs distincts pour effectuer des modifications de données en cascade.

Remarque

Étant donné que les déclencheurs s’exécutent dans une transaction, un échec à tout niveau d’un ensemble de déclencheurs imbriqués annule l’intégralité de la transaction et toutes les modifications de données sont restaurées. Incluez des instructions PRINT dans vos déclencheurs afin de pouvoir déterminer où l’échec s’est produit.

Déclencheurs récursifs

Un déclencheur AFTER ne s'appelle pas de manière récursive, sauf si l'option de base de données RECURSIVE_TRIGGERS est définie.

Il existe deux types de récursivité :

  • Récursivité directe

    Cette récursivité se produit lorsqu’un déclencheur se déclenche et effectue une action qui provoque le déclenchement du même déclencheur. Par exemple, une application met à jour la table T3 ; cela provoque le déclenchement de Trig3 . Trig3 met à jour à nouveau la table T3 ; cela provoque le déclenchement de Trig3 à nouveau.

    La récursivité directe peut également se produire lorsque le même déclencheur est appelé à nouveau, mais après un déclencheur d’un autre type (AFTER ou INSTEAD OF) est appelé. En d’autres termes, la récursivité directe d’un déclencheur INSTEAD OF peut se produire lorsque le même déclencheur INSTEAD OF est appelé pour une seconde fois, même si un ou plusieurs déclencheurs AFTER sont appelés entre eux. De même, la récursivité directe d’un déclencheur AFTER peut se produire lorsque le même déclencheur AFTER est appelé pour une seconde fois, même si un ou plusieurs déclencheurs INSTEAD OF sont appelés entre eux. Par exemple, une application met à jour la table T4. Cette mise à jour entraîne le déclenchement du déclencheur INSTEAD OF Trig4 . Trig4 met à jour la table T5. Cette mise à jour entraîne le déclenchement du déclencheur AFTER Trig5 . Trig5 met à jour la table T4, et cette mise à jour provoque à nouveau le déclenchement du déclencheur INSTEAD OF Trig4. Cette chaîne d’événements est considérée comme une récursivité directe pour Trig4.

  • Récursivité indirecte

    Cette récursivité se produit lorsqu’un déclencheur se déclenche et effectue une action qui provoque le déclenchement d’un autre déclencheur du même type (AFTER ou INSTEAD OF). Ce deuxième déclencheur effectue une action qui provoque le déclenchement du déclencheur d’origine à nouveau. En d’autres termes, la récursivité indirecte peut se produire lorsqu’un déclencheur INSTEAD OF est appelé pour une seconde reprise, mais seulement après qu’un autre déclencheur INSTEAD OF ait été appelé entre-temps. De même, la récursivité indirecte peut se produire lorsqu’un déclencheur AFTER est appelé pour une deuxième fois, mais pas avant qu’un autre déclencheur AFTER soit appelé entre-temps. Par exemple, une application met à jour la table T1. Cette mise à jour entraîne le déclenchement du déclencheur AFTER Trig1 . Trig1 met à jour la table T2 et cette mise à jour entraîne le déclenchement du déclencheur AFTER Trig2 . Trig2 met à jour la table T1, ce qui provoque le déclenchement à nouveau du déclencheur AFTER Trig1.

Seule la récursivité directe des déclencheurs AFTER est empêchée lorsque l’option de base de données RECURSIVE_TRIGGERS est définie sur OFF. Pour désactiver la récursivité indirecte des déclencheurs AFTER, définissez également l’option de serveur des déclencheurs imbriqués sur 0.

Exemples

L’exemple suivant montre l’utilisation de déclencheurs récursifs pour résoudre une relation d’auto-référencement (également appelée fermeture transitive). Par exemple, la table emp_mgr définit les éléments suivants :

  • Un employé (emp) dans une entreprise.

  • Responsable de chaque employé (mgr).

  • Nombre total d’employés dans la structure organisationnelle relevant de chaque employé (NoOfReports).

Un déclencheur UPDATE récursif peut être utilisé pour conserver la NoOfReports colonne up-to-date à mesure que les nouveaux enregistrements d’employés sont insérés. Le déclencheur INSERT met à jour la NoOfReports colonne de l’enregistrement du gestionnaire, qui met à jour de manière récursive la NoOfReports colonne d’autres enregistrements dans la hiérarchie de gestion.

USE AdventureWorks2012;  
GO  
-- Turn recursive triggers ON in the database.  
ALTER DATABASE AdventureWorks2012  
   SET RECURSIVE_TRIGGERS ON;  
GO  
CREATE TABLE dbo.emp_mgr (  
   emp char(30) PRIMARY KEY,  
    mgr char(30) NULL FOREIGN KEY REFERENCES emp_mgr(emp),  
    NoOfReports int DEFAULT 0  
);  
GO  
CREATE TRIGGER dbo.emp_mgrins ON dbo.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 dbo.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 dbo.emp_mgrupd ON dbo.emp_mgr FOR UPDATE  
AS  
IF UPDATE (mgr)  
BEGIN  
   UPDATE dbo.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 dbo.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 dbo.emp_mgr(emp, mgr) VALUES  
    ('Harry', NULL)  
    ,('Alice', 'Harry')  
    ,('Paul', 'Alice')  
    ,('Joe', 'Alice')  
    ,('Dave', 'Joe');  
GO  
SELECT emp,mgr,NoOfReports  
FROM dbo.emp_mgr;  
GO  
-- Change Dave's manager from Joe to Harry  
UPDATE dbo.emp_mgr SET mgr = 'Harry'  
WHERE emp = 'Dave';  
GO  
SELECT emp,mgr,NoOfReports FROM emp_mgr;  
  
GO

Voici les résultats avant la mise à jour.

emp                            mgr                           NoOfReports  
------------------------------ ----------------------------- -----------  
Alice                          Harry                          2  
Dave                           Joe                            0  
Harry                          NULL                           1  
Joe                            Alice                          1  
Paul                           Alice                          0

Voici les 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 configurer l'option de déclencheurs imbriqués

Pour définir l’option de base de données RECURSIVE_TRIGGERS

Voir aussi

CRÉER UN DÉCLENCHEUR (Transact-SQL)
Configurer l’option de configuration du serveur des déclencheurs imbriqués

  • Last updated on