Recursive Triggers
If a trigger defined in the Products table modifies data in the Employees table, and the Employees table has a trigger that in turn modifies the Products table, the trigger defined in the Products table will fire again. This situation is called indirect recursion, because a single statement forces multiple executions of the same trigger, through the execution of other triggers. This is a special case of nested triggers, and everything said about it in the preceding section can be applied to this case.
In some scenarios, it is possible to have direct recursion, when a table has a trigger that modifies some data in the table again. In this case, by default, SQL Server will not fire the trigger again, avoiding this direct recursion.
To enable trigger recursion in a database you must set the 'recursive triggers' option to 'true' at database level using the sp_dboption system stored procedure, or set the option RECURSIVE_TRIGGERS ON in the ALTER DATABASE statement. Listing 9.22 shows both statements.
Code Listing 9.22. You Can Enable Recursive Triggers at Database Level Only
|
-- Enable Recursive triggers in Northwind
EXEC sp_dboption 'Northwind', 'recursive triggers', 'true'
-- Disable Recursive triggers in Northwind
ALTER DATABASE Northwind
SET RECURSIVE_TRIGGERS OFF
Consider the typical hierarchical table where you save cost and budget breakdown of a project cost control system. Every row in this table has a single ID as primary key, but it refers to another row as a parent row, excluding the root row: the project itself. Any change on Cost or Budget in a row has to be escalated to the highest level, and you introduce costs only in rows with no children.
This strategy is very flexible, adjusting changes easily on the distribution of activities in the project. Listing 9.23 shows the code to implement this example .
Code Listing 9.23. Use Triggers to Maintain Hierarchical Data
|
-- Create the base table
CREATE TABLE CostBudgetControl (
ID int NOT NULL
PRIMARY KEY,
Name nvarchar(100) NOT NULL,
ParentID int NULL
REFERENCES CostBudgetControl(ID),
Cost money NOT NULL DEFAULT 0,
Budget money NOT NULL DEFAULT 0,
HasChildren bit DEFAULT 0)
-- Insert Cost Structure
-- Create a text file (Gas.txt)
-- with the following contents:
/*
1, Gas Pipeline Project, 0, 85601000.0000, 117500000.0000, 1
2, Engineering, 1, 800000.0000, 950000.0000, 1
3, Materials, 1, 23400000.0000, 28000000.0000, 1
4, Construction, 1, 61000000.0000, 88000000.0000, 1
5, Supervision, 1, 401000.0000, 550000.0000, 1
6, Line, 2, 300000.0000, 400000.0000, 0
7, Stations, 2, 500000.0000, 550000.0000, 0
8, Pipes, 3, 14500000.0000, 16000000.0000, 0
9, Machinery, 3, 8900000.0000, 12000000.0000, 0
10, Section A, 4, 31000000.0000, 47000000.0000, 1
11, Section B, 4, 30000000.0000, 41000000.0000, 1
12, Welding, 5, 200000.0000, 250000.0000, 0
13, Civil, 5, 145000.0000, 200000.0000, 0
14, Buildings, 5, 56000.0000, 100000.0000, 0
15, Civil works, 10, 20000000.0000, 30000000.0000, 0
16, Civil works, 11, 18000000.0000, 25000000.0000, 0
17, Pipeline, 10, 11000000.0000, 17000000.0000, 0
18, Pipeline, 11, 12000000.0000, 16000000.0000, 0
*/
BULK INSERT Northwind.dbo.CostBudgetControl
FROM 'C:Gas.txt'
WITH
(
FIELDTERMINATOR = ', ',
ROWTERMINATOR = '
'
)
GO
UPDATE CostBudgetControl
SET ParentID = NULL
WHERE ID = 1
GO
-- Create the recursive trigger
CREATE TRIGGER udtCostBudget
ON CostBudgetControl
AFTER UPDATE
AS
IF @@rowcount>0
UPDATE CostBudgetControl
SET Cost = Cost
+ ISNULL((SELECT SUM(Cost)
FROM Inserted
WHERE Inserted.ParentID = CostBudgetControl.ID), 0)
- ISNULL((SELECT SUM(Cost)
FROM Deleted
WHERE Deleted.ParentID = CostBudgetControl.ID), 0),
Budget = Budget
+ ISNULL((SELECT SUM(Budget)
FROM Inserted
WHERE Inserted.ParentID = CostBudgetControl.ID), 0)
- ISNULL((SELECT SUM(Budget)
FROM Deleted
WHERE Deleted.ParentID = CostBudgetControl.ID), 0)
WHERE ID IN
(SELECT ParentID
FROM Inserted
UNION
SELECT ParentID
FROM Deleted)
GO
-- Enable Recursive triggers
ALTER DATABASE Northwind
SET RECURSIVE_TRIGGERS ON
GO
-- Total Cost and Budget
-- Before the update
SELECT Cost, Budget
FROM CostBudgetControl
WHERE ID = 1
-- Update some cost
UPDATE CostBudgetControl
SET Cost = 12500000.0000
WHERE ID = 17
-- Total Cost and Budget
-- After the update listings;triggers;mainataining hierarchical
data>
SELECT Cost, Budget
FROM CostBudgetControl
WHERE ID = 1
GO
DROP TABLE CostBudgetControl
Cost Budget
--------------------- ---------------------
85601000.0000 117500000.0000
Cost Budget
--------------------- ---------------------
87101000.0000 117500000.0000