CODEWITHSUNDEEP
sql-serversql-server-2005triggers
Raymond Morphy
Raymond Morphy

Reputation: 2526

Disable update trigger in some situations

I have written an trigger for update, so every time an update occurs the trigger will run. but just for one of my update statements I don't want the trigger happens. how can I disable trigger temporarily?

Upvotes: 1

Views: 1924

Answers (4)

Regular Jo
Regular Jo

Reputation: 5510

Similar to Dalex's answer:

Another solution is to add a column, like Triggered. And use something like this in your trigger.

IF NOT UPDATE(Triggered)
BEGIN
  RETURN;
END

UPDATE() merely checks to see if the column was included in the trigger, and not whether or not the value was actually changed. If every query you want to to fire the trigger has Triggered = 1, it will fire. Queries that don't have this will not fire the trigger.

You could use an nvarchar column and use a reason field as both a flag and an explanation.

UPDATE Users
  SET Password = ..encrypted_password..,
      Reason = 'changed password'
WHERE UserID = ...

and then

IF NOT UPDATE(Reason)
BEGIN
  RETURN;
END

Wheres if you update the lastLogin field upon login, you might might exclude reason, tus the trigger won't fire.

UPDATE Users
  SET LastLogin = getDate()
WHERE UserID = ...

Upvotes: 0

Will A
Will A

Reputation: 24988

MSDN has an example of how to use CONTEXT_INFO to disable a trigger for a single session only.

Upvotes: 4

Dalex
Dalex

Reputation: 3625

You should change table and trigger. One of possible solutions is to add column to your table, eg Triggered. When you make "normal" update then you should insert 1. If you need to avoid update then triggered=0. Trigger code should be:

IF EXISTS(SELECT * FROM INSERTED WHERE triggered=1)
--here you trigger fired

Upvotes: 0

josephj1989
josephj1989

Reputation: 9709

Use disable trigger statement and then do your update as below

DISABLE TRIGGER Person.uAddress ON Person.Address;
update ....
ENABLE Trigger Person.uAddress ON Person.Address;

Upvotes: 0

Related Questions