Triggers in SQL Server are powerful tools that allow you to automate actions when certain events occur on a table. However, there may be times when you want to temporarily disable a trigger for a specific statement while keeping it active for other statements. In this article, we will explore two methods to dynamically suppress a trigger in SQL Server.

Method 1: Using a Temporary Table

The simplest way to accomplish this is by creating a temporary table before executing the statement that would fire the trigger. The trigger can then check for the existence of the temporary table and return without executing its code if the table exists. Here’s an example:

CREATE TABLE #Disable(ID INT)

-- Actual statement
INSERT dbo.Table1 VALUES(600)

DROP TABLE #Disable

In this example, the INSERT statement does not fire the trigger because the temporary table “#Disable” exists. Since the temporary table is local to the session, the trigger cannot be bypassed by other sessions.

While this method works, it does introduce overhead by using the Tempdb database to create and drop the temporary table.

Method 2: Using Context_Info()

Another way to dynamically suppress a trigger is by using the Context Info of the session. Context Info is a variable that belongs to the session and its value can be changed using the SET Context_Info command. Here’s an example:

SET Context_Info 0x55555

INSERT dbo.Table1 VALUES(100)

In this example, before issuing the INSERT statement, the context info is set to a specific value. In the trigger, we check if the value of the context info matches the declared value. If it does, the trigger simply returns without executing its code; otherwise, the trigger fires as usual.

This method avoids the overhead of creating and dropping a temporary table in the Tempdb database.

By using either of these methods, you can dynamically suppress a trigger in SQL Server for a specific statement while keeping it active for other statements. This gives you more control over the behavior of your triggers and allows for greater flexibility in your database operations.