Verwenden von geschachtelten Triggern

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.

  In SQL Server 2008 kann auch die direkte Rekursion 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 Trig4 betrachtet.

• 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 nested triggers-Serveroption auf 0 festlegen, 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 AdventureWorks2008R2;
GO
-- Turn recursive triggers ON in the database.
ALTER DATABASE AdventureWorks2008R2
   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

Im Folgenden sehen Sie die Ergebnisse vor der Aktualisierung.

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 der Aktualisierung.

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

• sp_configure (Transact-SQL)

So legen Sie die Datenbankoption RECURSIVE_TRIGGERS fest

• ALTER DATABASE (Transact-SQL)

Siehe auch

Verweis

Konzepte