Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Bypassing Triggers

By Andy Warren,

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!

If we execute the following statement, what trigger(s) will fire and how many times will they fire? Assume that the syntax is valid.
insert into testtable (rowid, somevalue) select top 10 rowid, somevalue from someothertable

Post your answer in the attached discussion forum. The best/most thorough answer gets a free copy of 'The Best of SQLServerCentral.com 2002'! Contest ends 1 week after this article is published.

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:

  • Session context information is stored in the context_info column in the master.dbo.sysprocesses table. This is a varbinary(128) column.
  • SET CONTEXT_INFO cannot be specified in a user-defined function. You cannot supply a null value to SET CONTEXT_INFO because the sysprocesses table does not allow null values.
  • SET CONTEXT_INFO does not accept expressions other than constants or variable names. To set the context information to the result of a function call, you must first place the function call result in a binary or varbinary variable.
  • When you issue SET CONTEXT_INFO in a stored procedure or trigger, unlike in other SET statements, the new value set for the context information persists after the stored procedure or trigger completes.

How's that for a few different techniques? Have you used any of these or maybe have another method you prefer? Add a comment, I usually reply the same day!

Total article views: 10515 | Views in the last 30 days: 5
 
Related Articles
FORUM

Trigger Problems, Transaction context in use by another session

Trigger Problems, Transaction context in use by another session

FORUM

execute triggers externally

can we execute triggers externally,

FORUM

Execution Contexts - way to examine? flush?

Execution Context Perf Counter read 4 billion, performance suffering

FORUM

Procedure not executing in trigger

problem when execute procedure in trigger

FORUM

trigger problem

trigger

Tags
triggers    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones