Learn more about SQL Server tools

   
   

















































Latest from MSSQLTips

Find Tables, Columns or Stored Procedures used in a SQL Server Reporting Services Report

Dynamically Refresh SQL Server Reporting Services Report Date Parameters

SQL Server 2016 T-SQL Syntax to Query Temporal Tables

Troubleshoot SQL Azure Bacpac Import Failures

On-Demand Webcast - Monitoring SQL Server with Mobile Devices

Configure the SQL Server Integration Services For Loop Container

On-Demand Webcast - Performance Monitoring with Uptime

On-Demand Webcast - Physical and Virtual Performance Monitoring with SQL Diagnostic Manager

On-Demand Webcast - Encrypting Data with SQL Server

On-Demand Webcast - Performance monitoring with Spotlight on SQL Server

Whitepaper - Resolving the Database Performance Blame Game

3 Tips for Managing Large Numbers of SQL Server Jobs

Accelerate SQL Server with Flash Storage














Disabling a Trigger for a Specific SQL Statement or Session

MSSQLTips author Samuel Vanga By:   |   Read Comments (8)   |   Related Tips: More > Triggers



>>> >> > Vote for your favorite MSSQLTips.com Authors < << <<<


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


Last Update: 9/25/2008


About the author
MSSQLTips author Samuel Vanga


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools


Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Wednesday, February 25, 2015 - 10:46:43 AM - Simon Jones Read The Tip

Excellent solution!  Though it is worth noting that the CONTEXT_INFO needs to be set back to 0x000000.... afterwards because the value remains persistent to the SPID which will potentially be recycled if CONNECTION POOLING is in use


Thursday, September 04, 2014 - 11:49:44 AM - Additional Notes Read The Tip
Note that for certain cases, there's also the option to not execute triggers with BCP, BULK INSERT, and even INSERT ... SELECT * FROM OPENROWSET(BULK...) with the WITH(IGNORE_TRIGGERS) table hint. See Controlling Trigger Execution When Bulk Importing Data http://technet.microsoft.com/en-us/library/ms187640%28v=sql.105%29.aspx

Wednesday, July 09, 2014 - 1:44:52 AM - JUAN MIGUEL Read The Tip

Thanks from Spain.

I plan use this mechanism in a stored procedure and there is a note in sql documentation (from Microsoft in Context_info topic) that worry me: 

(..) When you issue SET CONTEXT_INFO in a stored procedure or trigger, unlike in other SET statements, the new value set for the context information persists after the stored procedure or trigger is completed.

Signifies this than new Context_Info value will persist during time? Must I save and restore CONTEXT_info values? Can I Do?

Best Regards,

 

Juanmi


Wednesday, March 28, 2012 - 10:30:35 AM - Helio Read The Tip

Great!!

Thanks from Portugal.


Tuesday, March 20, 2012 - 9:26:05 AM - hrishikesh Read The Tip

Great Article...It really helped me solving my problem.


Thursday, September 09, 2010 - 2:31:13 PM - Kamlesh Read The Tip
Can we alter trigger on a linked server. and also is context_info() value will be available on linked server


Wednesday, October 01, 2008 - 11:39:47 AM - Mstarr Read The Tip

Great tip: didn't know about user-definable Context_Info.
I'm a minimalist, though....
instead of 4 lines:
DECLARE @Cinfo VARBINARY(128
SELECT @Cinfo Context_Info
() 
IF @Cinfo 
0x55555 
RETURN
The following seems to work as well:
IF (Context_info() = 0x55555) return

Cheers


Tuesday, September 30, 2008 - 11:31:26 AM - sandeepbattina Read The Tip

wonderful article,really helpful




 
Sponsor Information







 

Follow

Get Free SQL Tips

Twitter

LinkedIn

Google+

Facebook

Pinterest

RSS

Learning

DBAs

Developers

BI Professionals

Careers

Q and A

Today's Tip

Resources

Tutorials

Webcasts

Whitepapers

Tools

Search

Tip Categories

Search By TipID

Authors

Community

First Timer?

Pictures

Free T-shirt

Contribute

Events

User Groups

Author of the Year

More Info

Join

About

Copyright

Privacy

Disclaimer

Feedback

Advertise

Copyright (c) 2006-2015 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.