Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Race Condition with trigger

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-12-04 : 08:15:46
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

22859 Posts

Posted - 2006-12-04 : 08:25:00
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 - 2006-12-04 : 10:50:43
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 - 2006-12-04 : 11:03:41
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



Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 11:20:29
"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

15732 Posts

Posted - 2006-12-04 : 23:26:28
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

30421 Posts

Posted - 2006-12-05 : 00:44:11
Oh.
Make more sense too...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
   

- Advertisement -