SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Doubt About Insert Trigger?


Doubt About Insert Trigger?

Author
Message
harish_ravi
harish_ravi
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 76
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)

Group: General Forum Members
Points: 232266 Visits: 46356
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Matt Miller (4)
Matt Miller (4)
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30375 Visits: 19009
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 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.


...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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)

Group: General Forum Members
Points: 232266 Visits: 46356
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Steve Jones
Steve Jones
SSC Guru
SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)SSC Guru (150K reputation)

Group: Administrators
Points: 150364 Visits: 19453
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search