Home Forums SQL Server 2008 SQL Server 2008 - General Why is "instead of delete" trigger not fired by delete inside "after update" trigger RE: Why is "instead of delete" trigger not fired by delete inside "after update" trigger

  • 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 time

    2. the instead trigger fired first, then the after trigger for the first time

    For 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 http://connect.microsoft.com and link back to this thread. I think we have covered the topic quite well.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato