Use CONTEXT_INFO to avoid firing triggers

DML Triggers are commonly used to apply some business rules to the data in the table. The most common implementation would be updating the date_updated column automatically whenever the data in the table changes. For the illustration, this can be done with a following trigger:

CREATE TRIGGER dbo.MyAwesomeTrigger
	ON dbo.MyAwesomeTable
	AFTER UPDATE
	AS
	BEGIN
	   UPDATE t
	    SET date_updated = getutcdate()
	   FROM dbo.MyAwesomeTable t
	   INNER JOIN inserted i
	   on i.id = t.id
	END

However, there are times where we do not want the trigger to fire. For example, during maintenance task, data migration or database deployment, we may be running some updates that should not fire the trigger. Some applications can also be designed in such way that they do not always require the trigger to fire.

Please note that this post is not about whether disabling triggers is a good or bad idea. It is simply to illustrate a better way to handle such need

In such scenario, we would first disable the trigger in question, run the script, and then re-enable the trigger:

DISABLE TRIGGER dbo.MyAwesomeTrigger ON dbo.MyAwesomeTable;

UPDATE dbo.MyAwesomeTable
   SET MyAwesomeColumn = 1

ENABLE TRIGGER dbo.MyAwesomeTrigger ON dbo.MyAwesomeTable;

This approach however brings some risks:

1. We may forget to re-enable the trigger in a complex script.
2. The trigger may be already disabled before we start the script, in which case we should not be re-enabling it as part of our change.
3. The query may fail, and it may never reach the ENABLE TRIGGER statement which will leave it disabled.

If we were to account for the initial trigger status, our simple script would get much more complex:

DECLARE @is_disabled BIT

-- GET THE TRIGGER STATUS
SELECT @is_disabled = is_disabled 
FROM sys.triggers
WHERE name = 'MyAwesomeTrigger'


-- IF TRIGGER IS DISABLED BEFORE WE RUN THE QUERY, DO NOT ATTEMPT TO DISABLE IT AGAIN:
IF @is_disabled = 0
DISABLE TRIGGER dbo.MyAwesomeTrigger ON dbo.MyAwesomeTable;

-- RUN OUR SIMPLE UPDATE
UPDATE dbo.dMyAwesomeTable
SET name = 'test1'

-- IF TRIGGET WAS DISABLED BEFORE WE STARTED THE QUERY, DO NOT RE-ENABLE IT:
IF @is_disabled = 0
ENABLE TRIGGER dbo.MyAwesomeTrigger ON dbo.MyAwesomeTable;

Adding error handling to make sure we are re-enabling the trigger when the query fails:

DECLARE @is_disabled BIT

-- GET THE TRIGGER STATUS
SELECT @is_disabled = is_disabled 
FROM sys.triggers
WHERE name = 'MyAwesomeTrigger'


-- IF TRIGGER IS DISABLED BEFORE WE RUN THE QUERY, DO NOT ATTEMPT TO DISABLE IT AGAIN:
IF @is_disabled = 0
DISABLE TRIGGER dbo.MyAwesomeTrigger ON dbo.MyAwesomeTable;

BEGIN TRY
	-- RUN OUR SIMPLE UPDATE
	update dbo.MyAwesomeTable
        set name = CONVERT(INT,'X')
END TRY
BEGIN CATCH
	-- IF TRIGGET WAS DISABLED BEFORE WE STARTED THE QUERY, DO NOT RE-ENABLE IT:
	IF @is_disabled = 0
	ENABLE TRIGGER dbo.MyAwesomeTrigger ON dbo.MyAwesomeTable;
	THROW
	END CATCH

-- IF TRIGGET WAS DISABLED BEFORE WE STARTED THE QUERY, DO NOT RE-ENABLE IT:
IF @is_disabled = 0
ENABLE TRIGGER dbo.MyAwesomeTrigger ON dbo.MyAwesomeTable;

And the simple update has gotten quite ridiculous.

I do not advocate disabling triggers as they are there for a reason. If your application or business process has been designed in a way where you must often run a script with triggers disabled, you should design your triggers and scripts to handle this. One straightforward way to achieve it is to use CONTEXT_INFO()

CONTEXT_INFO simply assigns a custom binary payload to a given session. We can design our trigger to only fire where the CONTEXT_INFO does not contain our custom “secret” message:

CREATE TRIGGER dbo.MySuperAwesomeTrigger
	ON dbo.MyAwesomeTable
	AFTER UPDATE
	AS
	BEGIN
	   UPDATE t
	    SET date_updated = getutcdate()
	   FROM dbo.MyAwesomeTable t
	   INNER JOIN inserted i
	   on i.id = t.id
	   WHERE ISNULL(CONTEXT_INFO(),0x00000000) <> CONVERT(VARBINARY(128),'Hey there!')
	END

And then we can simply set the custom payload when running the update:

DECLARE @CONTEXT_INFO VARBINARY(128) = CONVERT(VARBINARY(128),'Hey there!')

SET CONTEXT_INFO @CONTEXT_INFO

UPDATE dbo.MyAwesomeTable
SET name = 'HERO'

As long as our “secret” custom payload is set, the trigger will never fire.