Sometimes being DBA you need to perform certain activities where we do not want that triggers should fire. for example in most of the cases where we are doing some kind of datafix we do not want unnecessary audit data generated. When we are not working in 24/7 environment its very easy we can drop/disable the trigger in off hours and then do the operation, but in 24/7 environment you do not have this luxury. So we need a mechanism to bypass the trigger action. In SQL server this action can be performed by utilizing CONTEXT_INFO session level setting. Let's take a UPDATE trigger example from one of my previous post.
We can modify the update trigger as shown below.
ALTER TRIGGER update_trigger
ON trigger_test1
FOR UPDATE AS
BEGIN
IF CONTEXT_INFO() = 0x545
RETURN
UPDATE a
SET value2= i.value1*2
FROM trigger_test1 a
JOIN INSERTED i
ON a.id = i.id
END
GO
Now If I want to perform UPDATE on the trigger_test1 table and I do not want trigger to fire, the only thing I need to do is to put SET CONTEXT_INFO statement in the beginning.
SET CONTEXT_INFO 0x545
UPDATE trigger_test1 SET value1 = 30 WHERE ID = 5
If You want to know how to perform testing on triggers then go through this article.
We can modify the update trigger as shown below.
ALTER TRIGGER update_trigger
ON trigger_test1
FOR UPDATE AS
BEGIN
IF CONTEXT_INFO() = 0x545
RETURN
UPDATE a
SET value2= i.value1*2
FROM trigger_test1 a
JOIN INSERTED i
ON a.id = i.id
END
GO
Now If I want to perform UPDATE on the trigger_test1 table and I do not want trigger to fire, the only thing I need to do is to put SET CONTEXT_INFO statement in the beginning.
SET CONTEXT_INFO 0x545
UPDATE trigger_test1 SET value1 = 30 WHERE ID = 5
If You want to know how to perform testing on triggers then go through this article.