I’m looking for an answer that satisfies my need on both SQL Server and MySQL.
I have a small, custom audit table that I will be writing to through triggers on various tables. There are situations where I would like to bypass those triggers. I know that I can make a column on the tables and use it as a sort of flag for the trigger, but I’m hoping to find something that generates less clutter.
Is it possible to prevent a trigger from being called using a variable in my statement or some other query side thing?
EDIT: To clarify, I’m not expecting an actual trigger/query that works on both platforms. I’m looking for a solution that fits the desired goal of not having to use an extra column in each table.
I answered a question like this back on
Jan 20, 2012 : Disable trigger for just one table
It was a workaround for MySQL I suggested. No negative feedback so far.
For SQL Server, a quick and dirty approach that we have used for a number of years without any problems is to use the existence of a temporary table that has a specific name (typically based on the trigger name) in order to provide an easy manual override that bypasses the trigger. The #temp table will be specific to this connection, but remains in scope in the trigger and can therefore be used as a way to pass information. If you are dealing with a very high concurrency OLTP type of environment you’ll want to test this approach and measure any overhead before proceeding in production.
Let’s say you have a trigger called
myTrigger. Here is what the code might look like when you want to bypass the trigger:
-- Create the marker table that indicates that the trigger should be bypassed CREATE TABLE #bypassTrigger_myTrigger (dummy BIT NOT NULL) GO -- Perform the action that would fire the trigger GO -- Drop the marker table DROP TABLE #bypassTrigger_myTrigger GO
And here is the logic that needs to go at the beginning of your trigger:
--If marker table is present, return immediately before performing any auditing IF OBJECT_ID('tempdb..#bypassTrigger_myTrigger') IS NOT NULL BEGIN PRINT('Bypassing myTrigger because #bypassTrigger_myTrigger marker table exists.') RETURN END