Recently at work we had a need to bypass a trigger on a key

table. That got me thinking that it was worth discussing as there are quite a

few alternatives. Most triggers are conditional based on the data itself. If the

where statement matches, something happens. Not quite as common is to do (or not

do) something based on external information - who the user is or what computer

issued the statement or maybe something even more complicated.

Before we begin, we haven't had a good puzzle/contest lately, so here goes!

insert into testtable (rowid, somevalue) select top 10 rowid, somevalue from someothertable

Now, back to our regularly scheduled article.... The easiest option is to disable the trigger. If you haven't done that

before, it's easy to do, just issue the following statement:

alter table tablename disable trigger triggername

And then do this to turn it back on:

alter table tablename enable trigger triggername

There are two downsides to this approach.

• The first and most critical is that you're disabling trigger execution

  for all users. Unless you can guarantee that no other users will make

  changes (the best method to guarantee that is to put the database in single

  user mode, less intrusive but less effective would be to temporarily deny

  access to all logins) you run the risk of having one or more changes bypass

  the trigger logic. Not good.

• The second is that if the table is replicated, it just won't work. SQL

  won't let you modify a replicated table. It will let you drop the

  trigger and put it back when you're done. It is possible to monkey

  with the system tables so that you can do the alter, but it hardly seems

  worth the effort. Dropping the trigger is ok other than the problems already

  mentioned, with the additional concern that adding it back may break things

  if you've configured the first/last trigger execution and this was one of

  those triggers.

A better approach is to put logic into the trigger that allows you to control

the execution. The best way is to use something that is independent of the data

in the table - username(), app_name(), and host_name() all come to mind. The

trigger code might then look something like this:

create trigger test on dbo.customers for update as
set nocount on
if app_name='MyAppName'
    begin
    --real trigger code here
    end

Given that your application sets the connection string to include AppName=MyAppName,

the trigger will execute normally for users, but if anyone connects with any

other application the main trigger code will not be executed. Sometimes that is

what you want, sometimes you may need to fine tune it, perhaps including

host_name and/or user_name() in the condition.

Think through your condition carefully. For example, in the scenario we were

working on there was a job that ran once a day to update rows matching our

criteria. In only that case did we want to bypass our code. We had other jobs

running as well, so that ruled out host, appname, and username - all were

running from the server under the agent account. That meant we needed a

different flag.

Note: We could have changed the job type to ActiveX and opened a new

connection, that would allowed us to change the appname. For this article I want

to illustrate some other alternatives that don't rely on those variables.

One common technique I use is to base actions on SPID from sysprocesses.

Every connection will have a unqiue spid and you can retrieve the value by doing

'select @@spid'. Of course you can't count on the spid being the same every time

so you have to have a way to configure your code. One way is like this:

create table TableName_Config (SPID int, BypassTrigger bit default 0)

Then when you want to bypass, you do this before you execute the code that

would cause the trigger to execute:

insert into TableName_Config (SPID, BypassTrigger) values (@@SPID, 1)

And the trigger code looks like this:

create trigger test on dbo.customers for update as
declare @Test bit
set nocount on
select @Test = BypassTrigger from dbo.TableName_Config where spid=@@SPID
if Test<>1
    begin
    --real trigger code here
    end

Not bad, right? You can execute the insert manually or based on some more

complicated condition in the calling code to set the behavior. Basically we just

need a flag, how simple or complex the code to set it is elsewhere, we keep the

trigger simple. Remember that you need to clean this up afterward and/or do a

delete/insert pair each time or you run the risk of the setting getting carried

over to another user that ends up with the same spid later on.

That brings us to a simplification of that technique. Do we really need a

table so we can look up a value? What we really need is a variable we can set

with the true/false value. We can't pass a variable in to the trigger, so it has

to be set before the trigger executes. Is there a way to do that?

Sure!

There is a relatively unknown set statement that will let you just that -

context_info. It let's you associate up to 128 bytes of binary data with a spid.

Instead of pushing a row into a table, you can set a variable that is only good

for the lifetime of the connection, nothing to clean up. To make it work, you do

something like this:

set context_info 0x1 (this is to bypass)

Then the trigger looks like this:

create trigger test on dbo.customers for update as
declare @Test varbinary(128)
set nocount on
select @Test = Context_INfo from master.dbo.sysprocesses where spid=@@SPID
if Test<>0x1
    begin
    --real trigger code here
    end

To save you a trip, here are the highlights from Books Online: