Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Doubt About Insert Trigger? Expand / Collapse
Author
Message
Posted Tuesday, May 27, 2008 1:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, August 9, 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
Posted Tuesday, May 27, 2008 1:56 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:16 AM
Points: 40,602, Visits: 37,056
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
Posted Tuesday, May 27, 2008 2:13 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:43 PM
Points: 7,179, Visits: 15,765
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
Posted Tuesday, May 27, 2008 2:20 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:16 AM
Points: 40,602, Visits: 37,056
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
Posted Tuesday, May 27, 2008 3:10 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:38 PM
Points: 31,355, Visits: 15,819
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 »

Add to briefcase

Permissions Expand / Collapse