1

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.

  • 4
    You're simply not going to get a trigger that works on both platforms, you need two different answers. – Aaron Bertrand Jun 5 '15 at 22:54
  • 1
    SQL-Server has statement based triggers, MySQL has only row based ones. SQL-Server has INSTEAD OF and AFTER triggers, MySQL has BEFORE and AFTER. There are numerous other differences that make the chance of writing a trigger that works in both platforms, almost 0. having said that, your idea of an extra column (or something else that someone else may come up with) may work for both worlds - but the implementation details will differ. – ypercubeᵀᴹ Jun 5 '15 at 23:16
  • 2
    In SQL Server, one method to bypass a trigger is to create a tempdb table before the DML and short-circuit execution within the trigger if the temp table exists. Another is to use set and get CONTEXT_INFO within the trigger. Both these methods would avoid adding a column to the table. – Dan Guzman Jun 6 '15 at 14:31
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.

  • What is the scope of the variable for that workaround? If it's only for my current connection or transaction that would be a suitable solution for the MySQL side of things. – Logarr Jun 8 '15 at 6:39
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.