SQLServerCentral.com / Development / SQL Server 2005 / Doubt About Insert Trigger? / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 10:47:40 GMT20RE: Doubt About Insert Trigger?http://www.sqlservercentral.com/Forums/Topic507231-145-1.aspxAhhhhhh, 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.Tue, 27 May 2008 15:10:20 GMTSteve Jones - SSC EditorRE: Doubt About Insert Trigger?http://www.sqlservercentral.com/Forums/Topic507231-145-1.aspxOh 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'Tue, 27 May 2008 14:20:50 GMTGilaMonsterRE: Doubt About Insert Trigger?http://www.sqlservercentral.com/Forums/Topic507231-145-1.aspx[quote][b]GilaMonster (5/27/2008)[/b][hr]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.[/quote]...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.Tue, 27 May 2008 14:13:40 GMTMatt Miller (#4)RE: Doubt About Insert Trigger?http://www.sqlservercentral.com/Forums/Topic507231-145-1.aspxBy 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.Tue, 27 May 2008 13:56:09 GMTGilaMonsterDoubt About Insert Trigger?http://www.sqlservercentral.com/Forums/Topic507231-145-1.aspxDoubt About Insert Trigger?I have a small doubt on Insert Trigger workingCreate 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?Tue, 27 May 2008 13:49:46 GMTharish_ravi