Bypassing a Trigger - SQL School Video

  • Andy Warren

    SSC Guru

    Points: 119675

    Comments posted to this topic are about the item Bypassing a Trigger - SQL School Video

  • Andy Warren

    SSC Guru

    Points: 119675

    This article is simple and to the point, but the "better" solution presented is still not very practical in many environments:

    - there is no reason why the person needing to run exempt admin queries would always be querying from the same computer, or why it would be only one person

    - changing the triggers every time, while possible, may not be an ideal solution either in many environments

    Is there any reason checking the username wouldn't be a better solution here? This is still not very extensible (in a secure environment every individual would need their own username, and therefore you would still be limiting the updates to one individual), but I don't see any disadvantages when compared to the hostname check.

    Ideally I would think you would want to do this with a Role - create or identify the role whose users should be exempt from the triggers firing, and add yourself to it?

    Again, in most production situations I would think you would also need to decide WHEN to avoid triggers firing (even for a single user on a single machine). The DBA might need to change data in individual records, and those updates should be properly logged, whereas they might later need to do a bulk update that does not qualitatively change the data, and not want that update logged.

    I believe this is really what the "SET CONTEXT_INFO" statement is for - identify an unused bit from the 128 available, for your environment, and also check for this in the trigger. That way the administrator that needs to do large-scale updates simply sets this context bit before doing the update - the rest of the time any updates they make do fire the trigger.

    Does anyone have any thoughts on the additional overhead of checking for role membership and CONTEXT_INFO content in every execution of a trigger? If the trigger were doing a very simple update, how much additional overhead would this represent?

    Are there any more efficient but reliable (and secure) solutions available?

    EDIT: After some online searching, a well-named temp table (or custom "context info" solution rather than the built-in 128 bits) is probably a better solution, as there is less risk of clashing with someone else's use of CONTEXT_INFO. Again, I have no idea what the performance impact would be, if any...

  • Anipaul

    SSC-Insane

    Points: 24681

    Nice article ...

  • Tao Klerks

    SSCarpal Tunnel

    Points: 4237

    strange - my post above was reassigned to Andy Warren! 🙂

    I guess there's a bug in the forums??

    http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.

  • Anipaul

    SSC-Insane

    Points: 24681

    Andy Warren (12/16/2008)


    This article is simple and to the point, but the "better" solution presented is still not very practical in many environments:

    - there is no reason why the person needing to run exempt admin queries would always be querying from the same computer, or why it would be only one person

    - changing the triggers every time, while possible, may not be an ideal solution either in many environments

    Is there any reason checking the username wouldn't be a better solution here? This is still not very extensible (in a secure environment every individual would need their own username, and therefore you would still be limiting the updates to one individual), but I don't see any disadvantages when compared to the hostname check.

    Ideally I would think you would want to do this with a Role - create or identify the role whose users should be exempt from the triggers firing, and add yourself to it?

    Again, in most production situations I would think you would also need to decide WHEN to avoid triggers firing (even for a single user on a single machine). The DBA might need to change data in individual records, and those updates should be properly logged, whereas they might later need to do a bulk update that does not qualitatively change the data, and not want that update logged.

    I believe this is really what the "SET CONTEXT_INFO" statement is for - identify an unused bit from the 128 available, for your environment, and also check for this in the trigger. That way the administrator that needs to do large-scale updates simply sets this context bit before doing the update - the rest of the time any updates they make do fire the trigger.

    Does anyone have any thoughts on the additional overhead of checking for role membership and CONTEXT_INFO content in every execution of a trigger? If the trigger were doing a very simple update, how much additional overhead would this represent?

    Are there any more efficient but reliable (and secure) solutions available?

    EDIT: After some online searching, a well-named temp table (or custom "context info" solution rather than the built-in 128 bits) is probably a better solution, as there is less risk of clashing with someone else's use of CONTEXT_INFO. Again, I have no idea what the performance impact would be, if any...

    Confused about whose comments is this??????????:w00t:

  • Tao Klerks

    SSCarpal Tunnel

    Points: 4237

    My comment (not that I'm very possessive about it 🙂 ) - I guess there's some sort of bug in the forum around edits.

    http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.

  • Andy Warren

    SSC Guru

    Points: 119675

    The article was originally and incorrectly loaded as my friend Brian's, when they corrected it I think it caused a problem with the post.

    You make good points about options. I've always liked doing it based on machine because I know that machine is "safe" for me to use to do certain things. It's not out of the question that I might have something else running under my credentials that I wouldn't want to bypass the trigger. The key point is that it's possible to get around the trigger without dropping/disabling, which is too often the way it's solved.

    Context info never really seemed to catch on, maybe because it requires a little more work to populate it.

  • olsonea

    SSC Enthusiast

    Points: 100

    Does wrapping the ALTER TABLE...DISABLE TRIGGER/UPDATE/ALTER TABLE...ENABLE TRIGGER in a transaction allow only your update statement to be executed with the triggers disabled?

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply