CODEWITHSUNDEEP
sql-server-2008triggers
dcparham
dcparham

Reputation: 291

SQL Server 2008 trigger update when specific column changes

Other solutions got me this far, but ultimately things are not working for me.

I have a column in tbl_activity named abstract. When tbl_activity.abstract changes, I want to set tbl_activity.flag = 0.

My 2 versions of the trigger will set flag = 0, but then I cannot set flag back to 1, because changing that flag causes it to trigger, and it keeps setting the flag = 0. I've tried comparing old to new values, deleted to inserted values, and tried INSTEAD OF INSERT.

Can someone please examine this code and tell me what I'm missing?

ALTER TRIGGER [dbo].[AbstractChange] --trigger name
ON [dbo].[cpy_activity] --table using the trigger
INSTEAD OF UPDATE, DELETE
AS 
     UPDATE a 
     SET a.flag = 0
     FROM tbl_activity a 
     INNER JOIN INSERTED i ON a.activityid = i.activityid
     INNER JOIN DELETED d ON d.activityid = i.activityid
     WHERE d.abstract NOT LIKE i.abstract

This results in "Command[s] completed successfully" - but when a column other than abstract changes, the trigger always sets flag = 0. Ideas?

Upvotes: 1

Views: 1434

Answers (2)

dcparham
dcparham

Reputation: 291

since the code i posted to ask my question, contained the erroneous use of INSTEAD OF - my "answer" at this point is that i changed my tbl_activity.abstract field from type Text to type Varchar(MAX) not null. One of the original problems was that the SQL trigger would not allow use of a text field. I will post that question properly on another post. Meanwhile, this code works, in the context where the "abstract" field is type varchar:

ALTER TRIGGER [dbo].[AbstractChange] --trigger name
ON  [dbo].[tbl_activity] --table using the trigger
AFTER INSERT, UPDATE
AS UPDATE a 
set a.flag = 0 --target action when above table is changed
FROM tbl_activity a 
 INNER JOIN INSERTED i ON a.activityid = i.activityid
 INNER JOIN DELETED d ON d.activityid = i.activityid
WHERE d.abstract <> i.abstract

Upvotes: 0

marc_s
marc_s

Reputation: 754488

Not sure what the datatype of abstract is - but I'm kinda guessing that the cause of your troubles is using the NOT LIKE operator in your trigger - try this instead:

ALTER TRIGGER [dbo].[AbstractChange] --trigger name
ON [dbo].[cpy_activity] --table using the trigger
INSTEAD OF UPDATE, DELETE
AS 
     UPDATE a 
     SET a.flag = 0
     FROM tbl_activity a 
     INNER JOIN INSERTED i ON a.activityid = i.activityid
     INNER JOIN DELETED d ON d.activityid = i.activityid
     WHERE d.abstract <> i.abstract   -- use the usual *not equal* operator <> here

Does that change anything?

Also: why is the trigger on cpy_activity, but inside the trigger, you're using tbl_activity - seems a bit odd, really .....

Upvotes: 1

Related Questions