Solved

Nested Triggers example needed in SQL Server !

Posted on 2009-07-16
7
662 Views
Last Modified: 2012-05-07
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
Comment
Question by:milani_lucie
  • 3
  • 2
7 Comments
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 200 total points
Comment Utility
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
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:milani_lucie
Comment Utility
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
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 300 total points
Comment Utility
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
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now