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...