Home > Uncategorized > Avoid Trigger Execution – Special Cases

Avoid Trigger Execution – Special Cases

Quick blog post on how to avoid trigger execution during some adhoc activity.

Today I needed to update around 12K rows in table which further had after update trigger. As a special case we didn’t want trigger to be executed after update. I knew I could do it by creating temp table in update  statement and then alter trigger to return if that temp table exists.. We tried that and it worked great.

But I still binge’d to see if there is any other way I could accomplish this . I saw same question in one forum and saw Context_Info() function mentioned as answer. But didn’t see any example code in the post so just for testing I ran couple of script to verify it and it works great too and also much cleaner way to get it done.

Scripts :

CREATE TABLE trtable (ID INT, gn VARCHAR(10))

GO

INSERT INTO trtable (ID, gn)

SELECT 1, ‘ab’
UNION ALL

SELECT 2, ‘bc’
UNION ALL

SELECT 3, ‘cd’

GO

–SELECT * FROM trtable

Trigger Script :

CREATE TRIGGER trigtest
ON trtable
AFTER UPDATE
AS
declare @c varbinary
set @c = CONTEXT_INFO()

If @c = 0x01
Begin
Return
End

EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘local’,
@recipients = ‘njandwani@local.com’,
@body = ‘Trigger executed.’,
@subject = ‘blah-blah-and-blah’;
GO

Now if you run below dml on trtable it won’t execute trigger code (In my trigger email part).

SET CONTEXT_INFO 0x01
INSERT INTO trtable(ID, Gn)

SELECT 4, ‘ef’

So here it is. Another way to skip trigger execution in some special cases. I didn’t get time to do further testing about behaviour in explicit transaction
or behaviour in other type of triggers. Will try to check it out if got time.

-NJ

Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: