Disabling a Trigger for a Specific SQL Statement or Session
Written By: Samuel Vanga -- 9/25/2008 -- 2 comments
-- printer friendly --
become a member
Problem
I have a trigger created on a table which will fire when either an INSERT, DELETE or UPDATE statements are executed against the table. I want to suppress the trigger from firing for a particular statement while it remains in its normal execution state for any of the other statements. Is there any way this can be done dynamically?
Solution
Disabling a trigger is something that you may need to do at certain times especially when performing admin tasks on a table. The best way to accomplish this is to use the following command to completely disable a trigger.
ALTER TABLE Table_Name DISABLE TRIGGER Trigger_Name |
The trigger once disabled will not fire until it is enabled again. To enable the trigger you use the following code:
ALTER TABLE Table_Name ENABLE TRIGGER Trigger_Name |
However, if you want to disable the trigger only for a particular statement there is no default mechanism to do this unless you develop your own programmatic approach. Using this kind of approach disables the trigger only for a specific statement while the trigger continues to fire for any of the other statements that hit the server at the same time.
Even though there are different ways to do it, the main logic lies in passing some kind of signal to the trigger that you do not want the trigger to fire.
Using a Temp Table
The simplest way to accomplish this is to create a temporary table before you execute the statement that would fire the trigger. Now the trigger will check for the existence of the temporary table and if the temporary table exists the trigger will return and not execute the code, else it will execute its code as normal.
To see how it works, run the following statements to create a table and a trigger.
USE AdventureWorks;
GO
-- creating the table in AdventureWorks database
IF OBJECT_ID('dbo.Table1') IS NOT NULL
DROP TABLE dbo.Table1
GO
CREATE TABLE dbo.Table1(ID INT)
GO
-- Creating a trigger
CREATE TRIGGER TR_Test ON dbo.Table1 FOR INSERT,UPDATE,DELETE
AS
IF OBJECT_ID('tempdb..#Disable') IS NOT NULL RETURN
PRINT 'Trigger Executed'
-- Actual code goes here
-- For simplicity, I did not include any code
GO |
If you do not want the trigger to fire for a statement, let the trigger know by creating the the temporary table in your statement.
CREATE TABLE #Disable(ID INT)
-- Actual statement
INSERT dbo.Table1 VALUES(600)
DROP TABLE #Disable |
You will notice that the INSERT statement did not fire the trigger and since the temporary table that was created is local to the session the trigger cannot be bypassed by any of the other sessions.
This works fine, but having to use the Tempdb database to create a temp table and then drop the temp table causes overhead which can be avoided. Using
Context_Info( )
Another way of accomplishing the task is to use the Context Info of the session. Context Info is a variable which belongs to the session. Its value can be changed using SET Context_Info
The trigger will mostly look like this:
USE AdventureWorks;
GO
-- creating the table in AdventureWorks database
IF OBJECT_ID('dbo.Table1') IS NOT NULL
DROP TABLE dbo.Table1
GO
CREATE TABLE dbo.Table1(ID INT)
GO
-- Creating a trigger
CREATE TRIGGER TR_Test ON dbo.Table1 FOR INSERT,UPDATE,DELETE
AS
DECLARE @Cinfo VARBINARY(128)
SELECT @Cinfo = Context_Info()
IF @Cinfo = 0x55555
RETURN
PRINT 'Trigger Executed'
-- Actual code goes here
-- For simplicity, I did not include any code
GO |
If you want to prevent the trigger from being executed you can do the following:
SET Context_Info 0x55555
INSERT dbo.Table1 VALUES(100) |
Before issuing the INSERT statement, the context info is set to a value. In the trigger, we are first checking if the value of context info is the same as the value declared. If yes, the trigger will simply return without executing its code, otherwise the trigger will fire.
Next Steps
- Read more about context info and find out what other useful things can be done with it
- If the table is being replicated, do not disable triggers on the publishing tables. Use the above presented approach instead.
- Other Trigger related tips
Readers Who Read This Tip Also Read
Comment or Ask Questions About This Tip
 Twitter This Tip!
|
