How to prevent a trigger from running on certain statements?

Question

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.

Answer 1

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.

Answer 2

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