How to Fire a Trigger on Demand
Published: Jun 16, 2014
Now, first of all, let me say that I’ve rarely found a good reason for needing to bypass a trigger or only wanting to fire one under certain circumstances (I have done this myself, but only in a cross site dual distributor replication using service broker… therefore not exactly a common requirement!!!), however, it is possible to achieve and surprisingly simple to do.

Basically all you need is to use context in your code by setting the context_info of your query as it runs. This will then be maintained for that spid.

By default all queries are run without context which you can see by running the following code:

select context_info()




Setting a context is incredibly easy to do:

set context_info 0x555

select context_info()




This will then be valid for the entire spid unless altered later in the same code. For example, in the following code each select statement is run under a different context.

set context_info 0x555

select context_info()

select 1

set context_info 0x444

select context_info()

select 1




So far you’ve seen that it’s possible to change a context, but not how it can be useful or make any difference to executing code. Well it can if you want it to.

Let’s make up a ridiculous example just to see how this can work.

Here’s a very simple table (with intentional spelling mistake):

use boredDBA
go

if object_id('testContext') is not null drop table testContext
go

create table dbo.testContext
(
  
id int,
  
firstName varchar(15),
  
lastName varchar(20)
)
insert into testContext
values('Homer', 'Sampson'),
   (
'Bart', 'Simpson'),
   (
'Lisa', 'Simpson')


Now, instead of using permissions correctly in a database, we’re going to enforce them in a trigger. What we’re trying to achieve is that no-one can modify the data in this table. However, once a month we have an automated process which needs to be able to modify as it pleases. So, noting that we have completely disregarded permissions as an alternative, how could we achieve this? We can use context.

Let’s create our trigger:

create trigger tr_denyModify on dbo.testContext
  
for insert, update, delete
as
   rollback transaction
go


Okay, if you read data then everything’s fine:

select *
from testContext
where id = 1




But if you attempt a modification then, correctly, you are denied by the trigger:

update testContext
set lastName = 'Simpson'
where id = 1




So let’s make a couple of changes to the trigger code:

alter trigger tr_denyModify on dbo.testContext
  
for insert, update, delete
as
   if
isnull(context_info(), 0) != 0x555
  
begin
       rollback transaction
   end
go


Now you can see that the trigger looks for a context and if the correct context has been passed in then it will allow data access. Therefore if you now run the original modification with context then you can see that you have effectively bypassed the trigger.

set context_info 0x555

update testContext
set lastName = 'Simpson'
where id = 1




select *
from testContext
where id = 1




It is also useful to note that you could have certain parts of a script for which you do want a trigger to fire and others that you don’t. You can therefore change the context as you go in order to accommodate this:

-- null
select context_info()

-- no context, so fails to the CATCH clause
begin try
  
update testContext
  
set firstName = 'Marge'
  
where id = 1
end try
begin catch
  
select 'Not allowed'
end catch

-- unchanged table
select *
from testContext
where id = 1

set context_info 0x555

-- context now changed, TRY will succeed
begin try
  
update testContext
  
set firstName = 'Marge'
  
where id = 1
end try
begin catch
  
select 'Not allowed'
end catch

-- modified table
select *
from testContext
where id = 1




Now, again, I’ve not yet (outside of my service broker ridiculousness) found a reasonable argument for “sometimes” wanting to fire a trigger. Just that this is how you CAN get around it if you really must.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron