Geneste triggers maken

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Zowel DML- als DDL-triggers worden genest wanneer een trigger een actie uitvoert waarmee een andere trigger wordt gestart. Deze acties kunnen andere triggers initiëren, enzovoort. DML- en DDL-triggers kunnen tot maximaal 32 niveaus genest worden. U kunt bepalen of AFTER-triggers kunnen worden genest via de geneste triggers serverconfiguratieoptie. IN PLAATS VAN triggers (alleen DML-triggers kunnen IN PLAATS VAN triggers zijn) kunnen worden genest, ongeacht deze instelling.

Notitie

Elke verwijzing naar beheerde code van een Transact-SQL-trigger telt mee als één niveau in de 32-niveaus nestlimiet. Methoden die vanuit beheerde code worden aangeroepen, tellen niet mee voor deze limiet.

Als geneste triggers zijn toegestaan en een trigger in de keten een oneindige lus start, dan wordt het nestingsniveau overschreden en wordt de trigger beëindigd.

U kunt geneste triggers gebruiken om nuttige huishoudfuncties uit te voeren, zoals het opslaan van een back-upkopie van rijen die worden beïnvloed door een eerdere trigger. U kunt bijvoorbeeld een trigger maken op PurchaseOrderDetail waarmee een back-upkopie wordt opgeslagen van de PurchaseOrderDetail rijen die door de delcascadetrig trigger zijn verwijderd. Als de delcascadetrig-trigger van kracht is, verwijdert het wissen van PurchaseOrderID 1965 uit PurchaseOrderHeader daardoor de bijbehorende rij of rijen uit PurchaseOrderDetail. Als u de gegevens wilt opslaan, kunt u een DELETE-trigger maken op PurchaseOrderDetail waarmee de verwijderde gegevens worden opgeslagen in een andere afzonderlijk gemaakte tabel, del_save. Bijvoorbeeld:

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

We raden u niet aan geneste triggers te gebruiken in een volgordeafhankelijk proces. Gebruik afzonderlijke triggers om gegevens trapsgewijs aan te passen.

Notitie

Omdat triggers worden uitgevoerd binnen een transactie, annuleert een fout op elk niveau van een set geneste triggers de hele transactie en worden alle gegevenswijzigingen teruggedraaid. Neem PRINT-instructies op in uw triggers, zodat u kunt bepalen waar de fout is opgetreden.

Recursieve triggers

Een AFTER-trigger roept zichzelf niet recursief aan, tenzij de RECURSIVE_TRIGGERS databaseoptie is ingesteld.

Er zijn twee typen recursie:

• Directe recursie

  Deze recursie treedt op wanneer een trigger wordt geactiveerd en een actie uitvoert die ervoor zorgt dat dezelfde trigger opnieuw wordt geactiveerd. Een applicatie werkt bijvoorbeeld tabel T3bij; hierdoor wordt trigger Trig3 geactiveerd. Trig3 werkt tabel T3 opnieuw bij; dit zorgt ervoor dat trigger Trig3 opnieuw wordt geactiveerd.

  Directe recursie kan ook optreden wanneer dezelfde trigger opnieuw wordt aangeroepen, maar nadat een trigger van een ander type (NA of IN PLAATS VAN) wordt aangeroepen. Met andere woorden, directe recursie van een INSTEAD OF-trigger kan optreden wanneer dezelfde INSTEAD OF-trigger een tweede keer wordt aangeroepen, zelfs als een of meer AFTER-triggers tussen worden aangeroepen. Op dezelfde manier kan directe recursie van een AFTER-trigger optreden wanneer dezelfde AFTER-trigger een tweede keer wordt aangeroepen, zelfs als een of meer INSTEAD OF-triggers ertussen worden aangeroepen. Een applicatie werkt bijvoorbeeld tabel T4bij. Deze update zorgt ervoor dat IN PLAATS VAN trigger Trig4- wordt geactiveerd. Trig4 werkt tabel T5-bij. Deze update zorgt ervoor dat AFTER-trigger Trig5 wordt geactiveerd. Trig5 update tabel T4-, en deze update veroorzaakt dat de INSTEAD OF trigger Trig4- opnieuw wordt geactiveerd. Deze reeks gebeurtenissen wordt beschouwd als directe recursie voor Trig4-.

• Indirecte recursie

  Deze recursie treedt op wanneer een trigger wordt geactiveerd en een actie uitvoert waardoor een andere trigger van hetzelfde type (NA of IN PLAATS VAN) wordt geactiveerd. Met deze tweede trigger wordt een actie uitgevoerd waardoor de oorspronkelijke trigger opnieuw wordt geactiveerd. Met andere woorden, indirecte recursie kan optreden wanneer een IN PLAATS VAN trigger voor een tweede keer wordt aangeroepen, maar niet totdat er een andere IN PLAATS VAN trigger ertussen is aangeroepen. Op dezelfde manier kan indirecte recursie optreden wanneer een AFTER-trigger voor een tweede keer wordt aangeroepen, maar niet totdat er een andere AFTER-trigger tussen wordt aangeroepen. Een applicatie werkt bijvoorbeeld tabel T1bij. Deze update activeert de AFTER-trigger Trig1. Trig1 werkt tabel T2bij, en deze update triggert AFTER trigger Trig2. Trig2 werkt op zijn beurt de tabel T1 bij, wat ertoe leidt dat AFTER-trigger Trig1- opnieuw wordt geactiveerd.

Alleen directe recursie van AFTER-triggers wordt voorkomen wanneer de RECURSIVE_TRIGGERS databaseoptie is ingesteld op UIT. Als u indirecte recursie van AFTER-triggers wilt uitschakelen, moet u ook de optie geneste triggers server instellen op 0.

Voorbeelden

In het volgende voorbeeld ziet u hoe u recursieve triggers gebruikt om een zelfverwijzende relatie op te lossen (ook wel transitieve sluiting genoemd). De tabel emp_mgr definieert bijvoorbeeld het volgende:

• Een werknemer (emp) in een bedrijf.

• De manager voor elke werknemer (mgr).

• Het totale aantal werknemers in de organisatiestructuur die onder elke werknemer vallen (NoOfReports).

Een recursieve UPDATE-trigger kan worden gebruikt om de NoOfReports-kolom met de up-to-datum bij te werken wanneer er nieuwe werknemerrecords worden ingevoerd. Met de INSERT-trigger wordt de kolom NoOfReports van de managerrecord bijgewerkt, waardoor de NoOfReports kolom met andere records in de beheerhiërarchie recursief wordt bijgewerkt.

USE AdventureWorks2022;  
GO  
-- Turn recursive triggers ON in the database.  
ALTER DATABASE AdventureWorks2022  
   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  

Dit zijn de resultaten vóór de update.

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

Dit zijn de resultaten na de update.

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

De optie geneste triggers instellen

• sp_configure (Transact-SQL)

Het instellen van de RECURSIVE_TRIGGERS-databaseoptie

• ALTER DATABASE SET Opties (Transact-SQL)

Zie ook

CREATE TRIGGER (Transact-SQL)
Configureer de serverconfiguratieoptie voor geneste triggers