Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Bypassing a Trigger - SQL School Video Expand / Collapse
Author
Message
Posted Tuesday, November 25, 2008 7:15 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 2:45 PM
Points: 6,794, Visits: 1,906
Comments posted to this topic are about the item Bypassing a Trigger - SQL School Video

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #608785
Posted Tuesday, December 16, 2008 12:55 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 2:45 PM
Points: 6,794, Visits: 1,906
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...


Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #620211
Posted Tuesday, December 16, 2008 10:39 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 16, 2014 4:46 AM
Points: 5,416, Visits: 1,400
Nice article ...


Post #620625
Posted Tuesday, December 16, 2008 11:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 12:38 PM
Points: 1,385, Visits: 1,249
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.
Post #620675
Posted Tuesday, December 16, 2008 9:29 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 16, 2014 4:46 AM
Points: 5,416, Visits: 1,400
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??????????



Post #620920
Posted Wednesday, December 17, 2008 1:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 12:38 PM
Points: 1,385, Visits: 1,249
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.
Post #621012
Posted Wednesday, December 17, 2008 5:40 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 2:45 PM
Points: 6,794, Visits: 1,906
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.


Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #621114
Posted Wednesday, May 18, 2011 9:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 11, 2012 10:44 PM
Points: 4, Visits: 71
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?
Post #1111092
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse