InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 07:45:40 GMT20http://www.sqlservercentral.com/Forums/Topic1430725-391-1.aspx[quote][b]brettstahlman (3/18/2013)[/b][hr][quote][b]opc.three (3/16/2013)[/b][hr]I agree in so far as I could not find anything in spec related to this behavior, but not that it signifies any type of bug. I do not have a problem with it since the behavior is consistent and predictable on 2008 R2 and 2012, the versions I tried my code samples in my previous posts on. The behavior kind of makes sense when you consider of the number of permutations implied by the various ways it could have been implemented. If recursive triggers is off, and after triggers can fire instead triggers (your original concern was that they do not), and in turn instead triggers can fire after triggers (currently they do, even with recursive triggers off, which makes sense) then in your scenario predictability could become an issue as we would have to provide special code in our after triggers to determine which scenario we were in:1. the scenario where an after trigger fired first, then the instead trigger, then the after trigger a second time2. the instead trigger fired first, then the after trigger for the first timeFor me the lack of documentation does not point to a bug, it points to a lack of documentation.[/quote]I suppose you and I just have very different notions of what constitutes a bug. Suffice it to say, most software developers would love to have a customer who takes your view of these things... ;-)[/quote]Maybe so. I can agree to disagree here. I guess I cannot reasonably hold a vendor (or any software developer to your point) to that standard. I am not saying that customers do not try (this thread is a case-in-point ;-)) but I cannot go where you're going with this particular one.[quote][quote]PS Lynn and I were typing at the same time so I figured I would add this upon reading his post. While the SQL Server documentation is amongst the best in terms of RDBMS platforms I have worked with, it is clear that both of us are acutely aware of some of its shortcomings, of which you are highlighting one. SQL Server is a vast product and sometimes the nuances of a feature are not well addressed, or explicitly addressed at all. If you are concerned with this particular shortcoming I would encourage you to open a ticket on [u][url]http://connect.microsoft.com[/url][/u] and link back to this thread. I think we have covered the topic quite well.[/quote]Agreed. I appreciate the time you took to investigate and offer your perspective...[/quote]The pleasure was all mine. Thanks for the dialogue. Discussions like this are why I hang out here. If you open a Connect item please post the link.Mon, 18 Mar 2013 14:56:42 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1430725-391-1.aspx[quote][b]brettstahlman (3/18/2013)[/b][hr][quote][b]Lynn Pettis (3/16/2013)[/b][hr][quote][b]brettstahlman (3/16/2013)[/b][hr][quote][b]Lynn Pettis (3/16/2013)[/b][hr][quote][b]brettstahlman (3/16/2013)[/b][hr][quote][b]opc.three (3/16/2013)[/b][hr]The reason why I disagree is because of the behavior of the after trigger when it is the instead trigger that is fired first. The instead trigger fires the after trigger, but even if the after trigger does something that should fire the original instead trigger on the same table, it does not fire regardless of the values of the nested or recursive settings.In your case the after trigger fires first, but similar to the case I just described it will not fire an instead trigger on the same table regardless of the values of the nested or recursive settings.Maybe the documentation has a hole in it, it certainly would not be the first time, but the behavior seems to be predictable and consistent. Just my two cents.[/quote]I'm not arguing that it's not predictable and consistent in the instance of SQL Server I'm currently running... But the same could be said of just about any bug in a specific instance of software. My point is that the only sort of predictable behavior we should rely upon as developers is that which can be predicted from the specification, and there is nothing in the spec indicating that the instead of trigger will not be invoked from an after trigger on the same table. Are we in agreement?[/quote]No.A good example of changes made by MS that comes to my mind is the WebTasks procedures. Fully documented and useable without special permissions. Then one day MS decides to change that behaviour in a SP and code that worked for many people suddenly broke. IIRC, this change wasn't even communicated prior to it being implemented. What does this have to do with this discussion? Everything.This is why you do regression testing when applying CU's, Service Packs, upgrades; to make sure that the behaviour of your code hasn't changed unexpectedly and with possible disastrous effects to your data. From what I have been reading, you are expecting 100% perfection in the MS documentation. Good luck with that.[/quote]I'm not sure why you said "No": I asked whether the previous poster agreed that the behavior and documentation were inconsistent; far from contradicting my assertion, you provided another example of this sort of inconsistency, and even seemed to be implying that I should ignore the spec, relying upon rigorous testing instead. Even if the spec is so unreliable as to make this a reasonable approach, it should be pointed out that you haven't contradicted my basic assertion. Recall that I didn't say a developer shouldn't test his implementation. Testing is necessary, but not sufficient. Additionally, the implementation should not rely upon undocumented "features", even if they seem to work consistently. As a general rule (your WebTasks example notwithstanding), such features are more likely to change in a non-backwards-compatible way than spec-conformant behavior...[/quote]The "No" may have been a misinterpretation of what I had read. With that, I apologize.What I spoke of was not an inconsistency, it was a significant change in behaviour of a well documented capability at the time. It was changed without any notice.What I am saying here is that you need to perform full regression testing on your application when applying CU's, service packs, or upgrades in you development and QA environments prior to applying them in production. This is needed to ensure that demonstrated behaviour (that which you currently expect and have seen) doesn't change and cause problems. If it does change, then you need to determine what changes you need to do to make to correct the problem.I would say you need to do this even if the documentation was 100% accurate now. There is nothing to say MS might not making changes that could change behaviour in ways you may not anticipate, and do so without much fanfare (if any at all).[/quote]That's a bit scary... ;-) Ah, well. I appreciate your time and perspective...[/quote]But SQL Server is still [i]vastly[/i] better on this point that Oracle. Every tiny subchange in Oracle, from say 11.n.n to 11.n.n+1 can cause [b]huge[/b] query and performance issues. While SQL Server has things come up from time to time, it's nothing close to as awful as Oracle in this area.Mon, 18 Mar 2013 14:22:27 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1430725-391-1.aspx[quote][b]Lynn Pettis (3/16/2013)[/b][hr][quote][b]brettstahlman (3/16/2013)[/b][hr][quote][b]Lynn Pettis (3/16/2013)[/b][hr][quote][b]brettstahlman (3/16/2013)[/b][hr][quote][b]opc.three (3/16/2013)[/b][hr]The reason why I disagree is because of the behavior of the after trigger when it is the instead trigger that is fired first. The instead trigger fires the after trigger, but even if the after trigger does something that should fire the original instead trigger on the same table, it does not fire regardless of the values of the nested or recursive settings.In your case the after trigger fires first, but similar to the case I just described it will not fire an instead trigger on the same table regardless of the values of the nested or recursive settings.Maybe the documentation has a hole in it, it certainly would not be the first time, but the behavior seems to be predictable and consistent. Just my two cents.[/quote]I'm not arguing that it's not predictable and consistent in the instance of SQL Server I'm currently running... But the same could be said of just about any bug in a specific instance of software. My point is that the only sort of predictable behavior we should rely upon as developers is that which can be predicted from the specification, and there is nothing in the spec indicating that the instead of trigger will not be invoked from an after trigger on the same table. Are we in agreement?[/quote]No.A good example of changes made by MS that comes to my mind is the WebTasks procedures. Fully documented and useable without special permissions. Then one day MS decides to change that behaviour in a SP and code that worked for many people suddenly broke. IIRC, this change wasn't even communicated prior to it being implemented. What does this have to do with this discussion? Everything.This is why you do regression testing when applying CU's, Service Packs, upgrades; to make sure that the behaviour of your code hasn't changed unexpectedly and with possible disastrous effects to your data. From what I have been reading, you are expecting 100% perfection in the MS documentation. Good luck with that.[/quote]I'm not sure why you said "No": I asked whether the previous poster agreed that the behavior and documentation were inconsistent; far from contradicting my assertion, you provided another example of this sort of inconsistency, and even seemed to be implying that I should ignore the spec, relying upon rigorous testing instead. Even if the spec is so unreliable as to make this a reasonable approach, it should be pointed out that you haven't contradicted my basic assertion. Recall that I didn't say a developer shouldn't test his implementation. Testing is necessary, but not sufficient. Additionally, the implementation should not rely upon undocumented "features", even if they seem to work consistently. As a general rule (your WebTasks example notwithstanding), such features are more likely to change in a non-backwards-compatible way than spec-conformant behavior...[/quote]The "No" may have been a misinterpretation of what I had read. With that, I apologize.What I spoke of was not an inconsistency, it was a significant change in behaviour of a well documented capability at the time. It was changed without any notice.What I am saying here is that you need to perform full regression testing on your application when applying CU's, service packs, or upgrades in you development and QA environments prior to applying them in production. This is needed to ensure that demonstrated behaviour (that which you currently expect and have seen) doesn't change and cause problems. If it does change, then you need to determine what changes you need to do to make to correct the problem.I would say you need to do this even if the documentation was 100% accurate now. There is nothing to say MS might not making changes that could change behaviour in ways you may not anticipate, and do so without much fanfare (if any at all).[/quote]That's a bit scary... ;-) Ah, well. I appreciate your time and perspective...Mon, 18 Mar 2013 14:18:43 GMTbrettstahlmanhttp://www.sqlservercentral.com/Forums/Topic1430725-391-1.aspx[quote][b]opc.three (3/16/2013)[/b][hr]I agree in so far as I could not find anything in spec related to this behavior, but not that it signifies any type of bug. I do not have a problem with it since the behavior is consistent and predictable on 2008 R2 and 2012, the versions I tried my code samples in my previous posts on. The behavior kind of makes sense when you consider of the number of permutations implied by the various ways it could have been implemented. If recursive triggers is off, and after triggers can fire instead triggers (your original concern was that they do not), and in turn instead triggers can fire after triggers (currently they do, even with recursive triggers off, which makes sense) then in your scenario predictability could become an issue as we would have to provide special code in our after triggers to determine which scenario we were in:1. the scenario where an after trigger fired first, then the instead trigger, then the after trigger a second time2. the instead trigger fired first, then the after trigger for the first timeFor me the lack of documentation does not point to a bug, it points to a lack of documentation.[/quote]I suppose you and I just have very different notions of what constitutes a bug. Suffice it to say, most software developers would love to have a customer who takes your view of these things... ;-)[quote]PS Lynn and I were typing at the same time so I figured I would add this upon reading his post. While the SQL Server documentation is amongst the best in terms of RDBMS platforms I have worked with, it is clear that both of us are acutely aware of some of its shortcomings, of which you are highlighting one. SQL Server is a vast product and sometimes the nuances of a feature are not well addressed, or explicitly addressed at all. If you are concerned with this particular shortcoming I would encourage you to open a ticket on [u][url]http://connect.microsoft.com[/url][/u] and link back to this thread. I think we have covered the topic quite well.[/quote]Agreed. I appreciate the time you took to investigate and offer your perspective...Mon, 18 Mar 2013 14:09:41 GMTbrettstahlmanhttp://www.sqlservercentral.com/Forums/Topic1430725-391-1.aspx[quote][b]brettstahlman (3/16/2013)[/b][hr][quote][b]Lynn Pettis (3/16/2013)[/b][hr][quote][b]brettstahlman (3/16/2013)[/b][hr][quote][b]opc.three (3/16/2013)[/b][hr]The reason why I disagree is because of the behavior of the after trigger when it is the instead trigger that is fired first. The instead trigger fires the after trigger, but even if the after trigger does something that should fire the original instead trigger on the same table, it does not fire regardless of the values of the nested or recursive settings.In your case the after trigger fires first, but similar to the case I just described it will not fire an instead trigger on the same table regardless of the values of the nested or recursive settings.Maybe the documentation has a hole in it, it certainly would not be the first time, but the behavior seems to be predictable and consistent. Just my two cents.[/quote]I'm not arguing that it's not predictable and consistent in the instance of SQL Server I'm currently running... But the same could be said of just about any bug in a specific instance of software. My point is that the only sort of predictable behavior we should rely upon as developers is that which can be predicted from the specification, and there is nothing in the spec indicating that the instead of trigger will not be invoked from an after trigger on the same table. Are we in agreement?[/quote]No.A good example of changes made by MS that comes to my mind is the WebTasks procedures. Fully documented and useable without special permissions. Then one day MS decides to change that behaviour in a SP and code that worked for many people suddenly broke. IIRC, this change wasn't even communicated prior to it being implemented. What does this have to do with this discussion? Everything.This is why you do regression testing when applying CU's, Service Packs, upgrades; to make sure that the behaviour of your code hasn't changed unexpectedly and with possible disastrous effects to your data. From what I have been reading, you are expecting 100% perfection in the MS documentation. Good luck with that.[/quote]I'm not sure why you said "No": I asked whether the previous poster agreed that the behavior and documentation were inconsistent; far from contradicting my assertion, you provided another example of this sort of inconsistency, and even seemed to be implying that I should ignore the spec, relying upon rigorous testing instead. Even if the spec is so unreliable as to make this a reasonable approach, it should be pointed out that you haven't contradicted my basic assertion. Recall that I didn't say a developer shouldn't test his implementation. Testing is necessary, but not sufficient. Additionally, the implementation should not rely upon undocumented "features", even if they seem to work consistently. As a general rule (your WebTasks example notwithstanding), such features are more likely to change in a non-backwards-compatible way than spec-conformant behavior...[/quote]The "No" may have been a misinterpretation of what I had read. With that, I apologize.What I spoke of was not an inconsistency, it was a significant change in behaviour of a well documented capability at the time. It was changed without any notice.What I am saying here is that you need to perform full regression testing on your application when applying CU's, service packs, or upgrades in you development and QA environments prior to applying them in production. This is needed to ensure that demonstrated behaviour (that which you currently expect and have seen) doesn't change and cause problems. If it does change, then you need to determine what changes you need to do to make to correct the problem.I would say you need to do this even if the documentation was 100% accurate now. There is nothing to say MS might not making changes that could change behaviour in ways you may not anticipate, and do so without much fanfare (if any at all).Sat, 16 Mar 2013 17:35:31 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1430725-391-1.aspxI agree in so far as I could not find anything in spec related to this behavior, but not that it signifies any type of bug. I do not have a problem with it since the behavior is consistent and predictable on 2008 R2 and 2012, the versions I tried my code samples in my previous posts on. The behavior kind of makes sense when you consider of the number of permutations implied by the various ways it could have been implemented. If recursive triggers is off, and after triggers can fire instead triggers (your original concern was that they do not), and in turn instead triggers can fire after triggers (currently they do, even with recursive triggers off, which makes sense) then in your scenario predictability could become an issue as we would have to provide special code in our after triggers to determine which scenario we were in:1. the scenario where an after trigger fired first, then the instead trigger, then the after trigger a second time2. the instead trigger fired first, then the after trigger for the first timeFor me the lack of documentation does not point to a bug, it points to a lack of documentation. PS Lynn and I were typing at the same time so I figured I would add this upon reading his post. While the SQL Server documentation is amongst the best in terms of RDBMS platforms I have worked with, it is clear that both of us are acutely aware of some of its shortcomings, of which you are highlighting one. SQL Server is a vast product and sometimes the nuances of a feature are not well addressed, or explicitly addressed at all. If you are concerned with this particular shortcoming I would encourage you to open a ticket on [u][url]http://connect.microsoft.com[/url][/u] and link back to this thread. I think we have covered the topic quite well.Sat, 16 Mar 2013 11:46:55 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1430725-391-1.aspx[quote][b]Lynn Pettis (3/16/2013)[/b][hr][quote][b]brettstahlman (3/16/2013)[/b][hr][quote][b]opc.three (3/16/2013)[/b][hr]The reason why I disagree is because of the behavior of the after trigger when it is the instead trigger that is fired first. The instead trigger fires the after trigger, but even if the after trigger does something that should fire the original instead trigger on the same table, it does not fire regardless of the values of the nested or recursive settings.In your case the after trigger fires first, but similar to the case I just described it will not fire an instead trigger on the same table regardless of the values of the nested or recursive settings.Maybe the documentation has a hole in it, it certainly would not be the first time, but the behavior seems to be predictable and consistent. Just my two cents.[/quote]I'm not arguing that it's not predictable and consistent in the instance of SQL Server I'm currently running... But the same could be said of just about any bug in a specific instance of software. My point is that the only sort of predictable behavior we should rely upon as developers is that which can be predicted from the specification, and there is nothing in the spec indicating that the instead of trigger will not be invoked from an after trigger on the same table. Are we in agreement?[/quote]No.A good example of changes made by MS that comes to my mind is the WebTasks procedures. Fully documented and useable without special permissions. Then one day MS decides to change that behaviour in a SP and code that worked for many people suddenly broke. IIRC, this change wasn't even communicated prior to it being implemented. What does this have to do with this discussion? Everything.This is why you do regression testing when applying CU's, Service Packs, upgrades; to make sure that the behaviour of your code hasn't changed unexpectedly and with possible disastrous effects to your data. From what I have been reading, you are expecting 100% perfection in the MS documentation. Good luck with that.[/quote]I'm not sure why you said "No": I asked whether the previous poster agreed that the behavior and documentation were inconsistent; far from contradicting my assertion, you provided another example of this sort of inconsistency, and even seemed to be implying that I should ignore the spec, relying upon rigorous testing instead. Even if the spec is so unreliable as to make this a reasonable approach, it should be pointed out that you haven't contradicted my basic assertion. Recall that I didn't say a developer shouldn't test his implementation. Testing is necessary, but not sufficient. Additionally, the implementation should not rely upon undocumented "features", even if they seem to work consistently. As a general rule (your WebTasks example notwithstanding), such features are more likely to change in a non-backwards-compatible way than spec-conformant behavior...Sat, 16 Mar 2013 11:41:13 GMTbrettstahlmanhttp://www.sqlservercentral.com/Forums/Topic1430725-391-1.aspx[quote][b]brettstahlman (3/16/2013)[/b][hr][quote][b]opc.three (3/16/2013)[/b][hr]The reason why I disagree is because of the behavior of the after trigger when it is the instead trigger that is fired first. The instead trigger fires the after trigger, but even if the after trigger does something that should fire the original instead trigger on the same table, it does not fire regardless of the values of the nested or recursive settings.In your case the after trigger fires first, but similar to the case I just described it will not fire an instead trigger on the same table regardless of the values of the nested or recursive settings.Maybe the documentation has a hole in it, it certainly would not be the first time, but the behavior seems to be predictable and consistent. Just my two cents.[/quote]I'm not arguing that it's not predictable and consistent in the instance of SQL Server I'm currently running... But the same could be said of just about any bug in a specific instance of software. My point is that the only sort of predictable behavior we should rely upon as developers is that which can be predicted from the specification, and there is nothing in the spec indicating that the instead of trigger will not be invoked from an after trigger on the same table. Are we in agreement?[/quote]No.A good example of changes made by MS that comes to my mind is the WebTasks procedures. Fully documented and useable without special permissions. Then one day MS decides to change that behaviour in a SP and code that worked for many people suddenly broke. IIRC, this change wasn't even communicated prior to it being implemented. What does this have to do with this discussion? Everything.This is why you do regression testing when applying CU's, Service Packs, upgrades; to make sure that the behaviour of your code hasn't changed unexpectedly and with possible disastrous effects to your data. From what I have been reading, you are expecting 100% perfection in the MS documentation. Good luck with that.Sat, 16 Mar 2013 11:20:34 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1430725-391-1.aspx[quote][b]opc.three (3/16/2013)[/b][hr]The reason why I disagree is because of the behavior of the after trigger when it is the instead trigger that is fired first. The instead trigger fires the after trigger, but even if the after trigger does something that should fire the original instead trigger on the same table, it does not fire regardless of the values of the nested or recursive settings.In your case the after trigger fires first, but similar to the case I just described it will not fire an instead trigger on the same table regardless of the values of the nested or recursive settings.Maybe the documentation has a hole in it, it certainly would not be the first time, but the behavior seems to be predictable and consistent. Just my two cents.[/quote]I'm not arguing that it's not predictable and consistent in the instance of SQL Server I'm currently running... But the same could be said of just about any bug in a specific instance of software. My point is that the only sort of predictable behavior we should rely upon as developers is that which can be predicted from the specification, and there is nothing in the spec indicating that the instead of trigger will not be invoked from an after trigger on the same table. Are we in agreement?Sat, 16 Mar 2013 09:35:54 GMTbrettstahlmanhttp://www.sqlservercentral.com/Forums/Topic1430725-391-1.aspxThe reason why I disagree is because of the behavior of the after trigger when it is the instead trigger that is fired first. The instead trigger fires the after trigger, but even if the after trigger does something that should fire the original instead trigger on the same table, it does not fire regardless of the values of the nested or recursive settings.In your case the after trigger fires first, but similar to the case I just described it will not fire an instead trigger on the same table regardless of the values of the nested or recursive settings.Maybe the documentation has a hole in it, it certainly would not be the first time, but the behavior seems to be predictable and consistent. Just my two cents.Sat, 16 Mar 2013 09:14:57 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1430725-391-1.aspx[quote][b]opc.three (3/15/2013)[/b][hr][quote][b]brettstahlman (3/15/2013)[/b][hr]I agree that the "instead of delete" trigger will not be fired, [b]but because the docs seem to indicate that it should be[/u], I don't feel comfortable relying on the behaviour. If it turns out to be an "accident" of the current SQL Server implementation, there's no guarantee that code relying on this behaviour will continue to work in subsequent releases...[/quote]Hmmm, can you point out those docs again please, the ones that indicate the "instead delete" trigger should fire when deleting data from the base table within an "after update" trigger, and the "after update" trigger was the first trigger to be fired on the base table?[/quote]See the definitions of nested triggers and recursive triggers below. Note in particular that according to the definition of recursive triggers, I have none - only nested triggers, which are enabled on my server. If you're not convinced that there is no recursion in my case, look carefully at the definition: in particular, note that the recursion always involves a single trigger: in the example, T1. In my case, there are 2 different triggers, and neither is called more than once.Under "DML Triggers" in BOL, there's this:[i]If an INSTEAD OF trigger defined on a table executes a statement against the table that would ordinarily fire the INSTEAD OF trigger again, the trigger is not called recursively.[/i]But note that it refers explicitly to an instead of trigger within an instead of trigger, and hence, does not apply in my case.The bottom line is, I have a non-recursive, nested trigger, which isn't firing, even though the nesting level is only 2 (limit 32), and there's nothing in the documentation that indicates it shouldn't. Unless you can point to some sort of exception in BOL, which is applicable to my use case, I can only conclude that the docs are wrong...BOL on Nested Triggers[i]Use the nested triggers option to control whether an AFTER trigger can cascade; that is, perform an action that initiates another trigger, which initiates another trigger, and so on. When nested triggers is set to 0, AFTER triggers cannot cascade. When nested triggers is set to 1 (the default), AFTER triggers can cascade to as many as 32 levels. INSTEAD OF triggers can be nested regardless of the setting of this option.[/i]BOL on Recursive Triggers[i]SQL Server also allows for recursive invocation of triggers when the RECURSIVE_TRIGGERS setting is enabled using ALTER DATABASE.Recursive triggers enable the following types of recursion to occur: Indirect recursionWith indirect recursion, an application updates table T1. This fires trigger TR1, updating table T2. In this scenario, trigger T2 then fires and updates table T1.Direct recursionWith direct recursion, the application updates table T1. This fires trigger TR1, updating table T1. Because table T1 was updated, trigger TR1 fires again, and so on.[/i]Sat, 16 Mar 2013 07:27:30 GMTbrettstahlmanhttp://www.sqlservercentral.com/Forums/Topic1430725-391-1.aspx[quote][b]brettstahlman (3/15/2013)[/b][hr]I agree that the "instead of delete" trigger will not be fired, [b]but because the docs seem to indicate that it should be[/u], I don't feel comfortable relying on the behaviour. If it turns out to be an "accident" of the current SQL Server implementation, there's no guarantee that code relying on this behaviour will continue to work in subsequent releases...[/quote]Hmmm, can you point out those docs again please, the ones that indicate the "instead delete" trigger should fire when deleting data from the base table within an "after update" trigger, and the "after update" trigger was the first trigger to be fired on the base table?Fri, 15 Mar 2013 22:02:02 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1430725-391-1.aspxI agree that the "instead of delete" trigger will not be fired, but because the docs seem to indicate that it should be, I don't feel comfortable relying on the behaviour. If it turns out to be an "accident" of the current SQL Server implementation, there's no guarantee that code relying on this behaviour will continue to work in subsequent releases...Fri, 15 Mar 2013 21:25:55 GMTbrettstahlmanhttp://www.sqlservercentral.com/Forums/Topic1430725-391-1.aspxWhat the code sample illustrates is that an AFTER trigger firing for an event listened for by an INSTEAD trigger on the same table will not fire that INSTEAD trigger, regardless of the recursion or nested triggers settings, which was the root of your concern: an AFTER UPDATE trigger that issues a delete against the base table does not fire the INSTEAD DELETE trigger on that table.[code="sql"]USE [master]GO-- comment the cases as needed--case 1ALTER DATABASE [YourDatabaseName] SET RECURSIVE_TRIGGERS ON WITH NO_WAITGOEXEC sys.sp_configure @configname = 'nested triggers', @configvalue = 1;RECONFIGURE;GO--case 2ALTER DATABASE [YourDatabaseName] SET RECURSIVE_TRIGGERS ON WITH NO_WAITGOEXEC sys.sp_configure @configname = 'nested triggers', @configvalue = 0;RECONFIGURE;GO--case 3ALTER DATABASE [YourDatabaseName] SET RECURSIVE_TRIGGERS OFF WITH NO_WAITGOEXEC sys.sp_configure @configname = 'nested triggers', @configvalue = 0;RECONFIGURE;GO--case 4ALTER DATABASE [YourDatabaseName] SET RECURSIVE_TRIGGERS OFF WITH NO_WAITGOEXEC sys.sp_configure @configname = 'nested triggers', @configvalue = 1;RECONFIGURE;GOUSE [YourDatabaseName]GOIF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.maintable') AND type IN ( N'U' ) ) DROP TABLE dbo.maintable;GOCREATE TABLE dbo.maintable ( triggername SYSNAME );GOINSERT INTO dbo.maintable ( triggername )VALUES ( N'test' )GOCREATE TRIGGER dbo.instead_delete_maintable ON dbo.maintable INSTEAD OF DELETEASBEGIN INSERT INTO maintable VALUES ( OBJECT_NAME(@@PROCID) );ENDGOCREATE TRIGGER dbo.after_update_maintable ON dbo.maintable AFTER UPDATEASBEGIN INSERT INTO maintable VALUES ( OBJECT_NAME(@@PROCID) );ENDGOUPDATE dbo.maintableSET triggername = 'updated'WHERE triggername = 'test';GOSELECT *FROM dbo.maintablego[/code]Thu, 14 Mar 2013 14:41:16 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1430725-391-1.aspxStill, the case you've constructed is fundamentally different, as it involves an insert within an insert, which is inherently recursive. Mine involves a delete within an after insert, which would appear to be completely non-recursive. "Nested" perhaps, but not recursive...Thu, 14 Mar 2013 14:23:35 GMTbrettstahlmanhttp://www.sqlservercentral.com/Forums/Topic1430725-391-1.aspxThat seems to be the ticket. The instead trigger is fired once, and the after trigger is what continues to recurse.[code="sql"]USE [master]GOALTER DATABASE [YourDatabaseName] SET RECURSIVE_TRIGGERS ON WITH NO_WAITGOUSE [YourDatabaseName]GOIF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.maintable') AND type IN ( N'U' ) ) DROP TABLE dbo.maintable;GOCREATE TABLE dbo.maintable ( triggername SYSNAME );GOCREATE TRIGGER dbo.instead_insert_maintable ON dbo.maintable INSTEAD OF INSERTASBEGIN INSERT INTO maintable VALUES ( OBJECT_NAME(@@PROCID) );ENDGOCREATE TRIGGER dbo.after_insert_maintable ON dbo.maintable AFTER INSERTASBEGINIF @@nestlevel < 20 INSERT INTO maintable VALUES ( OBJECT_NAME(@@PROCID) );ENDGOINSERT INTO dbo.maintable ( triggername )VALUES ( N'test' ) GOSELECT *FROM dbo.maintablego[/code]Thu, 14 Mar 2013 13:34:21 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1430725-391-1.aspx[quote]I am not convinced your use case qualifies as indirect recursion since the action in the AFTER trigger does not cause the INSTEAD trigger to fire. If it did, and that inturn caused the AFTER trigger to fire again I still do not think it would qualify an indirect. Here is why.[/quote]Actually, the case I described seems not to involve *any* recursion, according to the definition in the docs, since "sameness" in the context of recursion refers to triggers - not tables - and there's no reason either of the triggers in question would trigger more than once.Thu, 14 Mar 2013 10:59:50 GMTbrettstahlmanhttp://www.sqlservercentral.com/Forums/Topic1430725-391-1.aspx[quote][b]brettstahlman (3/14/2013)[/b][hr]Thanks. Hopefully someone from the SQL Server team will weigh in on this, as it appears there may be an error in the documentation/spec. I started to call it an ambiguity, but the documentation states unambiguously that RECURSIVE_TRIGGERS can't be used to prevent indirect recursion, and the case I've described qualifies as indirect recursion, according to the definition provided in the documentation.[/quote]I am not convinced your use case qualifies as indirect recursion since the action in the AFTER trigger does not cause the INSTEAD trigger to fire. If it did, and that inturn caused the AFTER trigger to fire again I still do not think it would qualify an indirect. Here is why.From [u][url=http://msdn.microsoft.com/en-us/library/ms190739(v=sql.105).aspx]http://msdn.microsoft.com/en-us/library/ms190739(v=sql.105).aspx[/url][/u]:[quote][b]Indirect recursion[/b]This recursion occurs when a trigger fires and performs an action that causes another trigger [u]of the same type[/u] (AFTER or INSTEAD OF) to fire. This second trigger performs an action that causes the original trigger to fire again. In other words, indirect recursion can occur when an INSTEAD OF trigger is called for a second time, but not until another INSTEAD OF trigger is called in between. [u]Likewise, indirect recursion can occur when an AFTER trigger is called for a second time, but not until another AFTER trigger is called in between.[/u] For example, an application updates table T1. This update causes AFTER trigger Trig1 to fire. Trig1 updates table T2, and this update causes AFTER trigger Trig2 to fire. Trig2 in turn updates table T1 that causes AFTER trigger Trig1 to fire again.Only direct recursion of AFTER triggers is prevented when the RECURSIVE_TRIGGERS database option is set to OFF. To disable indirect recursion of AFTER triggers, also set the nested triggers server option to 0.[/quote]I think this is a matter of entry point. It seems that when the entry point to the chain of triggers firing is an INSTEAD trigger direct recursion is in play. But if the first trigger to fire is an AFTER trigger then it cannot initiate direct recursion when the next trigger in the chain would be an INSTEAD trigger. I agree this is an odd distinction in the behavior and I cannot find any documentation to explain the effect.Thu, 14 Mar 2013 10:34:21 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1430725-391-1.aspxThanks. Hopefully someone from the SQL Server team will weigh in on this, as it appears there may be an error in the documentation/spec. I started to call it an ambiguity, but the documentation states unambiguously that RECURSIVE_TRIGGERS can't be used to prevent indirect recursion, and the case I've described qualifies as indirect recursion, according to the definition provided in the documentation.Thu, 14 Mar 2013 08:40:38 GMTbrettstahlmanhttp://www.sqlservercentral.com/Forums/Topic1430725-391-1.aspxI see what you mean. It appears when the AFTER trigger is the first one to fire, nothing carried out within that trigger done against the base table will fire an INSTEAD trigger on that base table. I tried different settings of nested and recursive triggers and could not get the INSTEAD trigger to fire and like you could not find anything in the docs explaining the behavior.Thu, 14 Mar 2013 08:23:48 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1430725-391-1.aspxThe case you have constructed does indeed involve direct recursion (as defined by the docs) and is fundamentally different from mine: you're executing an insert statement from within an instead of insert trigger; I'm executing a delete from within an after update. In my case, there would be direct recursion only if the instead of delete trigger performed an update on the table that was the target of the original update.Thu, 14 Mar 2013 05:35:24 GMTbrettstahlmanhttp://www.sqlservercentral.com/Forums/Topic1430725-391-1.aspx[quote][b]brettstahlman (3/13/2013)[/b][hr]If I understand correctly, the recursion is indirect, and hence, should not be prevented by RECURSIVE_TRIGGERS disabled (the setting in my database).[/quote]I do not think it is considered indirect. I have no documentation to point to, only evidence. If you enable recursive triggers and run your test you'll quickly see an error like:[quote]Msg 217, Level 16, State 1, Procedure YourAfterTriggerName, Line 5Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).[/quote][code="sql"]USE [master]GO-- try it with it ON and you'll see a recursive limit error--ALTER DATABASE [YourDatabaseName] SET RECURSIVE_TRIGGERS ON WITH NO_WAITALTER DATABASE [YourDatabaseName] SET RECURSIVE_TRIGGERS OFF WITH NO_WAITGOUSE [YourDatabaseName]GOIF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.maintable') AND type IN ( N'U' ) ) DROP TABLE dbo.maintable;GOCREATE TABLE dbo.maintable ( triggername SYSNAME );GOCREATE TRIGGER dbo.instead_insert_maintable ON dbo.maintable INSTEAD OF INSERTASBEGIN INSERT INTO maintable VALUES ( OBJECT_NAME(@@PROCID) );ENDGOCREATE TRIGGER dbo.after_insert_maintable ON dbo.maintable AFTER INSERTASBEGIN INSERT INTO maintable VALUES ( OBJECT_NAME(@@PROCID) );ENDGOINSERT INTO dbo.maintable ( triggername )VALUES ( N'test' ) GOSELECT *FROM dbo.maintablego[/code]Thu, 14 Mar 2013 00:38:43 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1430725-391-1.aspxHi Brett,as I understand your case is about nested triggers.Check the article:http://msdn.microsoft.com/en-us//library/ms190739.aspx"Nested TriggersTriggers can be nested to a maximum of 32 levels. If a trigger changes a table on which there is another trigger, the second trigger is activated and can then call a third trigger, and so on. If any trigger in the chain sets off an infinite loop, the nesting level is exceeded and the trigger is canceled. To disable nested triggers, set the nested triggers option of sp_configure to 0 (off). "Nested triggers are configured on the server side:select * from sys.configurations where name like 'nested triggers'If this value is set to 0, that means that nested triggers are disabled.To turn it off you can use next script:sp_CONFIGURE 'nested_triggers',0GORECONFIGUREGOThu, 14 Mar 2013 00:17:11 GMTzzalina0http://www.sqlservercentral.com/Forums/Topic1430725-391-1.aspxOne of my tables has both an "after update" and an "instead of delete" trigger. When I perform a delete within the after update trigger, the instead of delete trigger is not fired. Although I don't actually want the instead of delete trigger to fire in this particular case, I'm troubled by the fact that I haven't seen anything in the documentation that indicates it shouldn't. If I understand correctly, the recursion is indirect, and hence, should not be prevented by RECURSIVE_TRIGGERS disabled (the setting in my database).The only thing I found in the docs that seemed close to explaining what I'm seeing is the following from the SQL Server 2008 R2 documentation:[i]If an INSTEAD OF trigger defined on a table executes a statement against the table that would ordinarily fire the INSTEAD OF trigger again, the trigger is not called recursively. Instead, the statement is processed as if the table had no INSTEAD OF trigger and starts the chain of constraint operations and AFTER trigger executions. For example, if a trigger is defined as an INSTEAD OF INSERT trigger for a table, and the trigger executes an INSERT statement on the same table, the INSERT statement executed by the INSTEAD OF trigger does not call the trigger again. The INSERT executed by the trigger starts the process of performing constraint actions and firing any AFTER INSERT triggers defined for the table.[/i]This statement, however, applies only to the case in which the statement triggering the recursive call to the instead of delete trigger is itself within the instead of delete trigger. In my case, the delete statement in question is in an after update trigger, so the statement from the docs seems not to apply.I noticed that at least one other user has been puzzled by this behavior:http://www.sqlservercentral.com/Forums/Topic1387252-23-1.aspxStill no response to his post...Does anyone know what's going on here? I could explicitly disable triggers before executing the delete statement within the after update trigger, but I don't want to do this unnecessarily...Thanks,Brett S.Wed, 13 Mar 2013 21:40:06 GMTbrettstahlman