disabling a Trigger

Question

disabling a Trigger

Analystik

Valued Member

Points: 67
More actions
November 13, 2002 at 7:59 am

#101308

Hi,
I have a trigger that logs modifications (Ins, Upd, Del after). I have a situation where I don't want to log the mods on a particular event.
The problem is if I use the "alter table" command to disable the trigger, all users in all sessions will not log.
I was thinking of tricking the trigger in believing that I'm in a replication mode and use the "not for replication option" but I have no idea how I can accomplish that.
any hints will be appreciated
Alain

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

paulhumphris Hall of Fame Points: 3496 More actions · Answer 1

Since the trigger is obviously built on a specific table in your database you may like to consider adding a bit column to your table to indicate whether to fire the trigger or not.

The trigger when fired could then decide whether to "log the mods" or not and reset the bit field once complete.

A bit field should not cause you a data storage problem, but you should watch for recursive trigger calls.

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 2

I would say to modify your trigger's logic to not log the mod's you don't want logged. paul's idea would work, and seems sound enough, but if you could build the right conditional logic into your trigger, I don't see that an extra field would be needed.

What exactly does the trigger do now, and what exactly do you want it not to do? Could you post the trigger code?

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 3

In te past with have not fired triggers for specific users and user suser_sname() to conditionally run the trigger code.

i.e

IF suser_sname() <> 'MyUser'

BEGIN

do stuff in

here

END

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

Analystik Valued Member Points: 67 More actions · Answer 4

I've found a workaround using a mixture of different sugestions.

first set the context_info of the session

declare @BinarySessionId varbinary(128)

select @BinarySessionId = convert(varbinary(128), @@spid)

Set context_info @sessionid

Then add in the Trigger :

Declare @BinarySessionIdToSkip varbinary(128)

SELECT @BinarySessionIdToSkip = context_info

FROM master.dbo.sysprocesses

WHERE SPID=@@SPID

If @SessionIdToSkip <> convert(varbinary(128), @@spid)

begin

--do logging process

end

return

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 5

Wow I never new about set context_info. I really like it, we have a real fudge in place at the moment.

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons