• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 715
  • Last Modified:

Nested Triggers example needed in SQL Server !

Hi,

Can you please provide me simple example for "Nested Triggers" in SQL Server ? Please provide me basic example with explanation in simple terms so that i can understand it better.

Thanks
0
milani_lucie
Asked:
milani_lucie
  • 3
  • 2
2 Solutions
 
Mark WillsTopic AdvisorCommented:
Hi mwvisa1,

They are two slightly different questions... One is recursion, the other is nested, and yes they are related so could easily be addressed in the one question.

Nested triggers have to be turned on or off as a configuration setting :

sp_CONFIGURE 'nested_triggers',1  -- turns on and 0 will turn off
GO
RECONFIGURE
GO

Or, using SSMS, right-click a server, and then select Properties. On the Advanced page, set the Allow Triggers to Fire Others option to True (the default) or False.

Recursive triggers are a database setting :

ALTER DATABASE databasename SET RECURSIVE_TRIGGERS ON  -- or OFF

Interestingly, disabling the RECURSIVE_TRIGGERS setting only prevents direct recursions. Have to set the nested triggers server option to 0 by using sp_configure to prevent indirect recursions. Even more interesting is that to allow recursive triggers at all, you need to set the nested triggers server option to 1 by using sp_configure.

From Books online we see a definition for Nested triggers :

Triggers can be nested to a maximum of 32 levels. If a trigger changes a table on which there is another trigger, the second trigger is activated and can then call a third trigger, and so on. If any trigger in the chain sets off an infinite loop, the nesting level is exceeded and the trigger is canceled.

Recursion is a little different but closely related... Again from books on line :

Recursive triggers enable the following types of recursion to occur:

1) Indirect recursion :
With indirect recursion, an application updates table T1. This fires trigger TR1, updating table T2. In this scenario, trigger T2 then fires and updates table T1.

2) Direct recursion :
With direct recursion, the application updates table T1. This fires trigger TR1, updating table T1. Because table T1 was updated, trigger TR1 fires again, and so on.

The following example uses both indirect and direct trigger recursion. Assume that two update triggers, TR1 and TR2, are defined on table T1. Trigger TR1 updates table T1 recursively. An UPDATE statement executes each TR1 and TR2 one time. Additionally, the execution of TR1 triggers the execution of TR1 (recursively) and TR2. The inserted and deleted tables for a specific trigger contain rows that correspond only to the UPDATE statement that invoked the trigger.

0
 
Kevin CrossChief Technology OfficerCommented:
Thanks, Mark.  I thought so originally, which is why I commented in both; however, I later thought I made a mistake. :)

Definitely difference as recursive is just one type of nesting that can occur.
0
 
Kevin CrossChief Technology OfficerCommented:
Here is another explanation with sample code and example:
http://articles.techrepublic.com.com/5100-10878_11-1058912.html#

And since you were inquiring also on the levels in your other question, you may take note to the "TRIGGER_NESTLEVEL() function...[and]...@@NESTLEVEL global variable" which tell you at what you of nesting you are.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
milani_lucieAuthor Commented:
Can you please provide me simple example on :

1) Indirect recursion :
With indirect recursion, an application updates table T1. This fires trigger TR1, updating table T2. In this scenario, trigger T2 then fires and updates table T1.

Appreciate your time, help and patience !

Thanks
0
 
Kevin CrossChief Technology OfficerCommented:
Try this on for size.  Not a very real world example, but should do the trick.
CREATE TABLE vendor(id INT IDENTITY(1,1), vendor NVARCHAR(100), countAux INT DEFAULT (0));
CREATE TABLE vendorAUX(id INT, vendor NVARCHAR(100), aux NVARCHAR(100));
GO
 
DECLARE @id INT;
 
INSERT INTO vendor(vendor) VALUES('V123')
SET @id = SCOPE_IDENTITY();
 
INSERT INTO vendorAUX(id, vendor, aux) VALUES(@id, 'V123', 'test')
INSERT INTO vendorAUX(id, vendor, aux) VALUES(@id, 'V123', 'test some more')
INSERT INTO vendorAUX(id, vendor, aux) VALUES(@id, 'V123', 'test again')
GO
 
CREATE TRIGGER trg_vendor ON Vendor FOR UPDATE
AS
IF UPDATE(vendor)
BEGIN
	UPDATE a
	SET a.vendor = i.vendor
	FROM vendorAUX a 
	INNER JOIN inserted i ON i.id = a.id
END
GO
 
CREATE TRIGGER trg_count_aux ON VendorAUX FOR UPDATE
AS
	UPDATE v
	SET v.countAux = (SELECT COUNT(*) FROM vendorAUX WHERE id = v.id)
	FROM vendor v
	INNER JOIN inserted i ON i.id = v.id
GO
 
UPDATE vendor
SET vendor = 'V234'
WHERE vendor = 'V123';
 
SELECT * FROM vendorAUX;
 
SELECT * FROM vendor;

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
Indirect recursion is the worst kind, and if ever possible, need to avoid. It is so very easy to get into a loop, and really reflects on poor design (in my books anyway)...

mwvisa1 has provided a good example...

an update on Vendor Table fires the trigger on Vendor which updates VendorAux Table and fires the VendorAux trigger which updates Vendor Table.

0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now