SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Race Condition with trigger
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 12/04/2006 :  08:15:46  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
chetan writes "what heppens if we have written trigger on insert in which we again performing insert operation on same table?please provide me solution.

Best Regards,

Chetan S. Raut"

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/04/2006 :  08:25:00  Show Profile  Reply with Quote
My understanding is that within a trigger an INSERT to the original table will not re-fire the trigger.

Kristen
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 12/04/2006 :  10:50:43  Show Profile  Reply with Quote
Easy enough to test


SET NOCOUNT ON
USE AdventureWorks
GO

CREATE TABLE myTable99(Col1 int, Col2 datetime DEFAULT(GetDate()))
GO

CREATE TRIGGER myTrigger99 ON myTable99 FOR INSERT AS INSERT INTO myTable99(Col1) SELECT 0
GO

INSERT INTO myTable99(Col1) SELECT 1
GO

SELECT * FROM myTable99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO




Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 12/04/2006 :  11:03:41  Show Profile  Reply with Quote
Thought they might be able to cause chaos with a setting

From BOL 2k5

quote:

Recursive Triggers
SQL Server also allows for recursive invocation of triggers when the RECURSIVE_TRIGGERS setting is enabled using ALTER DATABASE.

Recursive triggers enable the following types of recursion to occur:

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.


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.

Note:
The previous behavior occurs only if the RECURSIVE_TRIGGERS setting is enabled by using ALTER DATABASE. There is no defined order in which multiple triggers defined for a specific event are executed. Each trigger should be self-contained.



Disabling the RECURSIVE_TRIGGERS setting only prevents direct recursions. To disable indirect recursion also, set the nested triggers server option to 0 by using sp_configure.

If any one of the triggers performs a ROLLBACK TRANSACTION, regardless of the nesting level, no more triggers are executed.

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. To disable nested triggers, set the nested triggers option of sp_configure to 0 (off). The default configuration allows for nested triggers. If nested triggers is off, recursive triggers is also disabled, regardless of the RECURSIVE_TRIGGERS setting set by using ALTER DATABASE.

Note:
When a Transact-SQL trigger executes managed code by referencing a CLR routine, type, or aggregate, this reference counts as one level against the 32-level nesting limit. Methods invoked from within managed code do not count against this limit.




Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam




Edited by - X002548 on 12/04/2006 11:04:29
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 12/04/2006 :  11:20:29  Show Profile  Visit SwePeso's Homepage  Reply with Quote
"Race condition"?
Try to win some points on mocking the actor from Seinfeld?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

robvolk
Most Valuable Yak

USA
15668 Posts

Posted - 12/04/2006 :  23:26:28  Show Profile  Visit robvolk's Homepage  Reply with Quote
hehehe.

Probably a Comp Sci major who finally got a job in the real world, and is still worrying too much about theory:

http://en.wikipedia.org/wiki/Race_condition
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 12/05/2006 :  00:44:11  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Oh.
Make more sense too...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000