Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Nested Triggers example needed in SQL Server !

Posted on 2009-07-16
7
Medium Priority
?
695 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
6 Comments
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 800 total points
ID: 24878215
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 60

Expert Comment

by:Kevin Cross
ID: 24880153
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 60

Expert Comment

by:Kevin Cross
ID: 24880206
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:milani_lucie
ID: 24880704
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 60

Accepted Solution

by:
Kevin Cross earned 1200 total points
ID: 24880851
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
ID: 24880961
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

876 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