Solved

Trigger behavior- general question

Posted on 2006-06-16
3
263 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

728 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