asked on

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

SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kevin Cross

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.

Kevin Cross

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.

milani_lucie

ASKER

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

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Wills

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.