Disable update trigger in some situations

I have written an trigger for up开发者_Python百科date, 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?

---

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

---

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;

---

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

---

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 = ...