Nested Triggers

Triggers are nested when a trigger performs an action that initiates another trigger, which can initiate another trigger, and so on. Triggers can be nested up to 32 levels, and you can control whether triggers can be nested through the nested triggers server configuration option.

If nested triggers are allowed and a trigger in the chain sets off an infinite loop, the nesting level is exceeded and the trigger terminates.

You can use nested triggers to perform useful housekeeping functions such as storing a backup copy of rows affected by a previous trigger. For example, you can create a trigger on titleauthor that saves a backup copy of the titleauthor rows that the delcascadetrig trigger deleted. With the delcascadetrig trigger in effect, deleting title_id PS2091 from titles deletes the corresponding row or rows from titleauthor. To save the data, you create a DELETE trigger on titleauthor that saves the deleted data into another separately created table, del_save.

CREATE TRIGGER savedel

    ON titleauthor

FOR DELETE

AS

    INSERT del_save

    SELECT * FROM deleted

It is not a good idea to use nested triggers in an order-dependent sequence. Use separate triggers to cascade data modifications.

Note Because triggers execute within a transaction, a failure at any level of a set of nested triggers cancels the entire transaction, and all data modifications are rolled back. Include PRINT statements in your triggers so that you can determine where the failure occurred.

Recursive Triggers

A trigger does not call itself recursively unless the Recursive Triggers database option is set. There are two different types of recursion:

• Direct recursion, which occurs when a trigger fires and performs an action that causes the same trigger to fire again. For example, an application updates table T3, which causes trigger Trig3 to fire. Trig3 updates table T3 again, which causes trigger Trig3 to fire again.
• Indirect recursion, which occurs when a trigger fires and performs an action that causes a trigger on another table to fire. This second trigger causes an update to occur on the original table, which causes the original trigger to fire again. For example, an application updates table T1, which causes trigger Trig1 to fire. Trig1 updates table T2, which causes trigger Trig2 to fire. Trig2 in turn updates table T1 which causes Trig1 to fire again.

Examples

One use for recursive triggers is on a table with a self-referencing relationship (also known as transitive closure). For example, the table emp_mgr defines:

• An employee (emp) in a company.
• Each employee's manager (mgr).
• The total number of employees in the organizational tree reporting to each employee (NoOfReports).

A recursive update trigger can be used to keep the NoOfReports column up-to-date as new employee records are inserted. The insert trigger updates the NoOfReports column of the manager record, which recursively updates the NoOfReports column of other records up the management hierarchy.

USE pubs

GO

-- Turn recursive triggers ON in the database.

EXECUTE sp_dboption 'pubs', 'recursive triggers', TRUE

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

Here are the results before update:

emp                            mgr                           NoOfReports

------------------------------ ----------------------------- -----------

Alice                          Harry                          2

Dave                           Joe                            0

Harry                          NULL                           1

Joe                            Alice                          1

Paul                           Alice                          0

Here are the results after update:

emp                            mgr                           NoOfReports

------------------------------ ----------------------------- -----------

Alice                          Harry                          2

Dave                           Harry                          0

Harry                          NULL                           2

Joe                            Alice                          0

Paul                           Alice                          0

To set the nested triggers option

To set the recursive triggers database option

See Also