CODEWITHSUNDEEP
sqlsql-server
Trevor Daniel
Trevor Daniel

Reputation: 3954

Conditional SQL Server trigger

I have just found an existing trigger on a table which is causing problems where it's impossible to change the value of "haschanged" to "0"...

create trigger [dbo].[haschangedTD_WFM_Client] 
on [dbo].[TD_WFM_Client]
for update 
as 
    update TD_WFM_Client 
    set haschanged = 1 
    from TD_WFM_Client
    inner join inserted on TD_WFM_Client.ID = inserted.ID

As you will probably notice, if I do the following SQL, the value of "haschanged" changes to "0", the trigger jumps in and changes it to a "1"

UPDATE dbo.TD_WFM_Client 
SET HasChanged = 0 
WHERE (Id = 560552)

So, I am trying to write a trigger that allows me to do that update SQL without the trigger firing..

I just cannot work out the logic and the syntax that would do it?

I am wondering if in fact it is possible at all?

Can any SQL gurus help?

Upvotes: 0

Views: 71

Answers (1)

user6256515
user6256515

Reputation: 102

You don't need to create a new trigger, just disable the trigger during your update statement:

DISABLE TRIGGER [dbo].[haschangedTD_WFM_Client] on [dbo].[TD_WFM_Client]

UPDATE dbo.TD_WFM_Client 
SET HasChanged = 0 
WHERE (Id = 560552)

ENABLE TRIGGER [dbo].[haschangedTD_WFM_Client] on [dbo].[TD_WFM_Client]

Upvotes: 2

Related Questions