November 25, 2008 at 7:15 pm
Comments posted to this topic are about the item Bypassing a Trigger - SQL School Video
December 16, 2008 at 12:55 am
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...
December 16, 2008 at 10:39 am
Nice article ...
December 16, 2008 at 11:33 am
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.
December 16, 2008 at 9:29 pm
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:
December 17, 2008 at 1:44 am
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.
December 17, 2008 at 5:40 am
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.
May 18, 2011 at 9:21 am
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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply