Erstellen von geschachtelten Triggern

  • Artikel

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed Instance

Sowohl DML-Trigger als auch DDL-Trigger werden geschachtelt, wenn ein Trigger eine Aktion ausführt, die einen anderen Trigger auslöst. Diese Aktionen können andere Trigger auslösen usw. DML- und DDL-Trigger können bis auf 32 Ebenen geschachtelt werden. Sie können über die Geschachtelte Trigger -Serverkonfigurationsoption steuern, ob AFTER-Trigger geschachtelt werden können. INSTEAD OF-Trigger (nur DML-Trigger können INSTEAD OF-Trigger sein) können unabhängig von dieser Einstellung geschachtelt werden.

Hinweis

Jeder Verweis auf verwalteten Code aus einem Transact-SQL-Trigger zählt als eine Ebene für die Schachtelungsgrenze auf 32 Ebenen. Methoden, die aus verwaltetem Code aufgerufen werden, werden nicht mitgezählt.

Wenn geschachtelte Trigger zulässig sind und ein Trigger in der Kette eine Endlosschleife einleitet, wird die Anzahl der maximal zulässigen Schachtelungsebenen überschritten und der Trigger demzufolge beendet.

Sie können geschachtelte Trigger verwenden, um nützliche Verwaltungsfunktionen durchzuführen, wie z. B. das Speichern einer Sicherungskopie von Zeilen, die von einem vorherigen Trigger betroffen sind. Es ist beispielsweise möglich, einen Trigger für PurchaseOrderDetail zu erstellen, der eine Sicherungskopie der PurchaseOrderDetail -Zeilen speichert, die vom delcascadetrig -Trigger gelöscht wurden. Wenn der delcascadetrig -Trigger wirksam ist, führt das Löschen von PurchaseOrderID 1965 aus PurchaseOrderHeader dazu, dass die entsprechende(n) Zeile(n) aus PurchaseOrderDetailgelöscht werden. Zum Speichern der Daten erstellen Sie einen DELETE-Trigger für PurchaseOrderDetail , der die gelöschten Daten in einer getrennt erstellten Tabelle, del_save, speichert. Beispiel:

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

Das Verwenden geschachtelter Trigger wird nicht für eine Sequenz empfohlen, in der die Reihenfolge wichtig ist. Verwenden Sie getrennte Trigger, um Datenänderungen kaskadierend weiterzugeben.

Hinweis

Da Trigger innerhalb einer Transaktion ausgeführt werden, führt ein Fehler auf einer beliebigen Ebene einer Reihe geschachtelter Trigger zum Abbruch der gesamten Transaktion und zum Rollback für alle Datenänderungen. Fügen Sie PRINT-Anweisungen in die Trigger ein, sodass Sie ermitteln können, wo der Fehler aufgetreten ist.

Rekursive Trigger

Ein AFTER-Trigger kann sich nur dann rekursiv aufrufen, wenn die Datenbankoption RECURSIVE_TRIGGERS festgelegt wurde.

Die folgenden zwei Rekursionsarten stehen zur Verfügung:

  • Direkte Rekursion

    Diese Rekursion tritt auf, wenn ein Trigger ausgelöst wird und eine Aktion ausführt, die das erneute Auslösen desselben Triggers verursacht. Eine Anwendung aktualisiert z. B. die T3-Tabelle, wodurch der Trig3 -Trigger ausgelöst wird. Trig3 aktualisiert T3 erneut, sodass der Trig3 -Trigger erneut ausgelöst wird.

    Die direkte Rekursion kann auch auftreten, wenn derselbe Trigger erneut aufgerufen wird, jedoch erst nach dem Aufruf eines weiteren Triggers, der einen anderen Typ aufweist (AFTER oder INSTEAD OF). Mit anderen Worten: Die direkte Rekursion eines INSTEAD OF-Triggers kann auftreten, wenn derselbe INSTEAD OF-Trigger ein zweites Mal aufgerufen wird, auch wenn zwischendurch mindestens ein AFTER-Trigger aufgerufen wird. Auf gleiche Weise kann die direkte Rekursion eines AFTER-Triggers auftreten, wenn derselbe AFTER-Trigger ein zweites Mal aufgerufen wird, auch wenn zwischendurch mindestens ein INSTEAD OF-Trigger aufgerufen wird. Beispielsweise aktualisiert eine Anwendung Tabelle T4. Durch dieses Update wird das Auslösen von INSTEAD OF-Trigger Trig4 verursacht. Trig4 aktualisiert Tabelle T5. Durch dieses Update wird das Auslösen von AFTER-Trigger Trig5 verursacht. Trig5 aktualisiert wiederum Tabelle T4. Dieses Update verursacht erneut das Auslösen von INSTEAD OF-Trigger Trig4 . Diese Kette von Ereignissen wird als direkte Rekursion für Trig4betrachtet.

  • Indirekte Rekursion

    Diese Rekursion tritt auf, wenn ein Trigger ausgelöst wird, der eine Aktion ausführt, die das Auslösen eines anderen Triggers des gleichen Typs verursacht (AFTER oder INSTEAD OF). Dieser zweite Trigger führt eine Aktion aus, die das erneute Auslösen des ursprünglichen Triggers bewirkt. Mit anderen Worten: Die indirekte Rekursion tritt auf, wenn ein INSTEAD OF-Trigger ein zweites Mal aufgerufen wird, jedoch erst, wenn in der Zwischenzeit ein anderer INSTEAD OF-Trigger aufgerufen wird. Die indirekte Rekursion kann gleichermaßen auftreten, wenn ein AFTER-Trigger ein zweites Mal aufgerufen wird, jedoch erst, wenn in der Zwischenzeit ein anderer AFTER-Trigger aufgerufen wird. Beispielsweise aktualisiert eine Anwendung Tabelle T1. Durch dieses Update wird das Auslösen von AFTER-Trigger Trig1 verursacht. Trig1 aktualisiert Tabelle T2. Dieses Update verursacht wiederum das Auslösen von AFTER-Trigger Trig2 . Trig2 aktualisiert nun wiederum Tabelle T1 , wodurch der AFTER-Trigger Trig1 erneut ausgelöst wird.

Es wird nur die direkte Rekursion von AFTER-Triggern verhindert, wenn die Datenbankoption RECURSIVE_TRIGGERS auf OFF festgelegt ist. Sie müssen auch die Geschachtelte Trigger -Serveroption auf 0festlegen, um die indirekte Rekursion von AFTER-Triggern zu deaktivieren.

Beispiele

Das folgende Beispiel zeigt die Verwendung rekursiver Trigger zum Auflösen einer auf sich selbst verweisenden Beziehung (auch als transitiver Abschluss bezeichnet). Die emp_mgr -Tabelle definiert z. B. Folgendes:

  • Einen Angestellten (emp) in einem Unternehmen.

  • Den Vorgesetzten jedes Angestellten (mgr).

  • Die Gesamtzahl der Angestellten in der Hierarchie, die jedem einzelnen Vorgesetzten unterstellt sind (NoOfReports).

Mithilfe eines rekursiven UPDATE-Triggers kann die NoOfReports -Spalte auf dem aktuellen Stand gehalten werden, wenn neue Angestelltendatensätze eingefügt werden. Der INSERT-Trigger aktualisiert die NoOfReports -Spalte für den Datensatz des Vorgesetzten, wodurch rekursiv die NoOfReports -Spalte anderer Datensätze auf den höheren Hierarchieebenen aktualisiert wird.

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

Im Folgenden sehen Sie die Ergebnisse vor dem Update.

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

Im Folgenden sehen Sie die Ergebnisse nach dem Update.

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

So legen Sie die Option für geschachtelte Trigger fest

So legen Sie die Datenbankoption RECURSIVE_TRIGGERS fest

Weitere Informationen

CREATE TRIGGER (Transact-SQL)
Konfigurieren der Serverkonfigurationsoption Geschachtelte Trigger