May 27, 2008 at 1:49 pm
Doubt About Insert Trigger?
I have a small doubt on Insert Trigger working
Create a table "tab1" with 2 columns.
First column is auto-increment column starting with 1 and an increment of 1.
Second column is a integer column.
If we write an insert trigger on this empty table.
And in the trigger code if we write code for inserting one more row into the same table.
What will happen?
I better know that the process runs indefinetly and may be terminated after a couple of minutes.
After the job is terminated how many rows of data can be seen in the table?
May 27, 2008 at 1:56 pm
By default triggers cannot fire themselves. (recursive triggers disabled) so 2 rows will go in, but the second won't fire the trigger
If you have recursive triggers enabled then the trigger will fire itself to a max recursion of level permitted (I think 32) will throw an error and will roll back the entire transaction, including the initial row that fired the first trigger.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 27, 2008 at 2:13 pm
GilaMonster (5/27/2008)
By default triggers cannot fire themselves. (recursive triggers disabled) so 2 rows will go in, but the second won't fire the triggerIf you have recursive triggers enabled then the trigger will fire itself to a max recursion of level permitted (I think 32) will throw an error and will roll back the entire transaction, including the initial row that fired the first trigger.
...unless you manage to have a process whereby the "second" insertion process has something slightly different, allowing you to perform the insert only x amount of times before the "escape clause" fires. Like, say - a column called "original" which would be set to one value for the original inserts, and something different for the recursive insert.
This could allow you to insert multiple "near copies", an then have some escape clause, breaking the loop. Assuming that happens before the 32-recursion depth value, then everything could commit and you'd have multiple rows in your table.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 27, 2008 at 2:20 pm
Oh sure.
The 'hit recursion limit and roll back' was based on the assumption that there's no 'escape clause', that the trigger's been written to recurse 'forever'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 27, 2008 at 3:10 pm
Ahhhhhh, don't write triggers that go back on themselves. I might be called by your company and don't want to deal with that.
show us some code, or explain what you want to do (or what's happening) and we can help you figure this out.
Triggers fire once per insert (one row or many) in general.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply