Solved

Trigger behavior- general question

Posted on 2006-06-16
3
258 Views
Last Modified: 2010-08-05
I have a trigger on a table, that is called when a column is updated.   Within the trigger, I have some code that does an update to the same table/column.

It appears that the trigger only fires the first time, and does not get invoked when an update is ran from within the trigger.  

I'm guessing that this is desired and expected behavior, because you would not want a trigger to get in an endless loop.  Could someone please confirm this?  Is there a way that you could over-ride the behavior if you needed to?  (not to put it into an endless loop, but just for the trigger to fire because of the second update).

Any help greatly appreciated.
td

0
Comment
Question by:down0041
3 Comments
 
LVL 13

Accepted Solution

by:
devsolns earned 500 total points
ID: 16922326
This is from Books Online....you are talking about a recursive trigger.  You would be worrying about direct recursion.


Info:

Recursive Triggers
SQL Server also allows recursive invocation of triggers when the recursive triggers setting is enabled in sp_dboption.

Recursive triggers allow two types of recursion to occur:

Indirect recursion


Direct recursion
With indirect recursion, an application updates table T1, which fires trigger TR1, updating table T2. In this scenario, trigger T2 then fires and updates table T1.

With direct recursion, the application updates table T1, which fires trigger TR1, updating table T1. Because table T1 was updated, trigger TR1 fires again, and so on.

This 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. In addition, the execution of TR1 triggers the execution of TR1 (recursively) and TR2. The inserted and deleted tables for a given trigger contain rows corresponding only to the UPDATE statement that invoked the trigger.



Note  The above behavior occurs only if the recursive triggers setting of sp_dboption is enabled. There is no defined order in which multiple triggers defined for a given event are executed. Each trigger should be self-contained.


Disabling the recursive triggers setting only prevents direct recursions. To disable indirect recursion as well, set the nested triggers server option to 0 using sp_configure.

If any of the triggers do a ROLLBACK TRANSACTION, regardless of the nesting level, no further triggers are executed.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16922362
you can specify wether you trigger fires once or repeatedly (Recursively)

you control direct trigger recursion at the database level via a dboption ...

direct recursion is where your trigger directly reaffects the base table...

indirect recursion is where your trigger updates another table and a trigger on that
(or a subsequent "triggered" table... reupdates the first table and re-invokes your trigger...)

indrect recursion cannot be "switched" off..

in either case trigger invocation is limited to a depth of 32 ...  (sql 2000)...

 sp_dboption
nested triggers Option
Use the nested triggers option to control whether a trigger can cascade (perform an action that initiates another trigger that initiates another trigger, and so on). When nested triggers is set to 0, triggers cannot cascade. When nested triggers is set to 1 (the default), triggers can cascade to as many as 32 levels.

The setting takes effect immediately (without a server stop and restart).

 
0
 

Author Comment

by:down0041
ID: 16922365
Thank you.  That is exactly what I needed.
td
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 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

18 Experts available now in Live!

Get 1:1 Help Now