15

I am working on SQL Server 2008 R2.

I have a table benefit which has a AFTER INSERT, UPDATE trigger named tiu_benefit.

I want to write an UPDATE statement for this table to update 1 row but I dont want its trigger to fire. I know I can disable trigger before UPDATE and then enable the trigger after UPDATE :

DISABLE TRIGGER tiu_benefit ON benefit;  
GO  
UPDATE benefit SET editor = 'srh' where benefit_id = 9876
GO
ENABLE TRIGGER tiu_benefit ON benefit;  
GO  

But this disable and enabling trigger will affect all users logged in currently. So there is a possibility that another user run an UPDATE/INSERT while the trigger is disabled by my script which is not good. Thats why I only want to disable and enabling trigger for my current session. Is it possible? If yes please tell how.

Thanks

  • 1
    If you cannot modify your trigger, then the answer is no. – jyao Apr 18 '18 at 15:50
6

I did some testing on this and I think you'd be fine if you run your process in a single transaction.

BEGIN TRANSACTION
GO

DISABLE TRIGGER tiu_benefit ON benefit;
GO

UPDATE benefit
SET editor = 'srh'
WHERE benefit_id = 9876
GO

ENABLE TRIGGER tiu_benefit ON benefit;
GO

--Decide to commit or rollback

--commit
--rollback 

In my testing, I only highlighted and executed the BEGIN TRANSACTION and the DISABLE TRIGGER first. I then opened up a new (second) query window and tried to run various DML statements (SELECT, INSERT, UPDATE DELETE) against the base table. All attempts to access the base table in the second query window waited on the locks held by the window with the explicit transaction. Once I committed (or rolled back) my explicit transaction, the second window was able to access the table.

  • This will work, but the locks may cause unintended problems downstream, depending on how long you keep the transaction open. – CaM Apr 18 '18 at 20:17
  • @CaM - I would assume that a one row update wouldn't take too long assuming the OP commits or rolls back the transaction quickly. Hopefully, there is an index on benefit_id :) – Scott Hodgin Apr 18 '18 at 20:20
  • really liked this solution as I dont have to make any changes to the trigger – srh Jul 16 '18 at 14:21
18

To solve your problem, we have to take a programmatic approach to the problem. There are two routes you can go here. The reason for needing these approaches is because you cannot disable a trigger for a particular statement, it can only be disabled for the entirety of the table.

Option 1: Context_Info()

Samuel Vanga on MS SQL Tips had a great example:

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

Now when Samuel does not want want the trigger to execute, they use this:

SET Context_Info 0x55555 
INSERT dbo.Table1 VALUES(100)

Context_Info uses the following system views to grab information regarding the current session:

  • sys.dm_exec_requests

  • sys.dm_exec_sessions

  • sys.sysprocesses

The ideology here is that the binary string you are setting is exposed only to the current session, so when the trigger executes during your session, it will see the scope and variable setting of the Context_info function and it will jump to the escape portion of the trigger instead.

Option 2: Temp Table

Itzik Ben-Gan has a great solution in his book "Inside Microsoft SQL Server 2008 T-SQL Programming: T-SQL Programming" which is also in his later book T-SQL Querying. The primary problem with this over the context_info function is the minor TempDB overhead.

To spoil the surprise but not ruin the plot of the books (I felt they are worth purchasing and reading), you will alter your trigger.

Your trigger should perform a check for a temporary table. If the temporary table exists, the trigger should know to end and not perform the actions.

In the update statement you want to perform, create the temporary table first. It will be seen in the same transaction as the trigger and it will cause the trigger to ignore your statement.

Example of trigger:

CREATE TRIGGER TRIGGERNAME ON TABLENAME for INSERT AS

IF OBJECT_ID('tempdb..#FAKETEMPTABLE') IS NOT NULL RETURN;
GO

Example of beginning statement when you don't want the trigger to run:

CREATE TABLE #FAKETEMPTABLE(col1 SMALLINT);

Putting it altogether for your example:

ALTER TRIGGER tiu_benefit ON benefit FOR 
... 
AS
...
IF OBJECT_ID('tempdb..#FAKETEMPTABLE') IS NOT NULL RETURN;
--... rest of code here
GO

CREATE TABLE #FAKETEMPTABLE(col1 SMALLINT);
UPDATE benefit SET editor = 'srh' where benefit_id = 9876;
GO
  • 2
    I would use context_info() instead of temp table in the trigger. In another word, if a trigger detects the context_info returns a specific value, the trigger would function accordingly. You may refer the relevant SO question here: stackoverflow.com/questions/3025662/… – jyao Apr 18 '18 at 15:58
  • 1
    You could also put in a check similar to context_info using original_login() to tell the trigger to never run if a specific person is hitting the trigger. – Kenneth Fisher Apr 30 '18 at 15:07
2

I would use either CONTEXT_INFO or the newer SESSION_CONTEXT. Both are session based values.

  • CONTEXT_INFO is a single VARBINARY(128) value. This has been available since at least SQL Server 2000. CONTEXT_INFO is viewable by anyone with VIEW SERVER STATE as it is a field returned by the sys.dm_exec_sessions DMV. I have used this one before and it works quite well.

    Set via SET CONTEXT_INFO
    Get via CONTEXT_INFO() or sys.dm_exec_sessions

    Depending on the type of value that you are storing to CONTEXT_INFO, there are some nuances to be aware of. I cover that in the following blog post:

    Why Doesn’t CONTEXT_INFO() Return the Exact Value Set by SET CONTEXT_INFO?

  • Session_context is a key/value pair of SQL_VARIANT values. This was introduced in SQL Server 2016. The separation of values for different purposes is quite nice. Session_context is only viewable by the current session.

    Set this value via sp_set_session_context
    Get this value via SESSION_CONTEXT

One thing to consider regarding the local temporary table option and even the disable / enable Trigger option: both of those require some amount of locking and tran log activity. Both of those options increase the potential for contention, even if minimally. The two "context" options should be lighter weight / memory-only.

  • context_info is a pain reliever, whenever you want to run a production data change this comes in handy, especially disabling the trigger can cause other operations not to fire the trigger. – Biju jose Apr 19 '18 at 8:36

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.