<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Development
»
Doubt About Insert Trigger?
Doubt About Insert Trigger?
Rate Topic
Display Mode
Topic Options
Author
Message
harish_ravi
harish_ravi
Posted Tuesday, May 27, 2008 1:49 PM
Grasshopper
Group: General Forum Members
Last Login: Sunday, August 09, 2009 11:55 PM
Points: 24,
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?
Post #507231
GilaMonster
GilaMonster
Posted Tuesday, May 27, 2008 1:56 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 10:47 AM
Points: 37,729,
Visits: 29,990
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 2008, MVP
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
Post #507234
Matt Miller (#4)
Matt Miller (#4)
Posted Tuesday, May 27, 2008 2:13 PM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 7:57 PM
Points: 6,998,
Visits: 13,949
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?
Post #507248
GilaMonster
GilaMonster
Posted Tuesday, May 27, 2008 2:20 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 10:47 AM
Points: 37,729,
Visits: 29,990
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 2008, MVP
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
Post #507250
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Tuesday, May 27, 2008 3:10 PM
SSC-Dedicated
Group: Administrators
Last Login: Today @ 10:52 AM
Points: 31,431,
Visits: 13,740
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
Post #507292
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
