UPDATE Trigger when specific fields change

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

UPDATE Trigger when specific fields change

Author

Topic

hztm2
Starting Member

16 Posts

Posted - 2010-02-18 : 08:19:47

I am new to sql server triggers and wondered if somebody could help please...I want to trigger when the CreditLimit or Status fields change value but the trigger always runs when any field changes - I want to update only when CreditLimit or Status change value. The code is like below:

CREATE TRIGGER name
ON Sales
FOR UPDATE AS
IF UPDATE(CreditLimit) OR UPDATE(Status)
BEGIN
INSERT INTO tblChanged SELECT
Name, CreditLimit, Status
FROM inserted
END

Thank you very much for any assistance.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-18 : 08:24:01

Looks like your app is updateting all columns with its old values...
INSERT INTO tblChanged
SELECT
i.Name, i.CreditLimit, i.Status
FROM inserted i
join deleted d
on i.keyfield = d.keyfield
where
i.CreditLimit <> d.CreditLimit or
i.Status <> d.Status

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-02-18 : 08:38:44

UPDATE() only means that the column was included in the UPDATE statement - NOT that any data in that column has, necessarily, changed.

You may need to also allow for CreditLimit / Status being NULL


where 
       COALESCE(i.CreditLimit, -1) <> COALESCE(d.CreditLimit, -1)
    OR COALESCE(i.Status, -1 <> COALESCE(d.Status, -1)

Choose a value where I have shown "-1" which is of the correct datatype, but a value that will never exist in the actual data

hztm2
Starting Member

16 Posts

Posted - 2010-02-18 : 08:40:52

Thank you very much indeed for your assistance. This looks exactly what I require.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources