Conditional SQL Trigger?

Question

i need to create an Update Trigger, that will only work when my record's status is different then the last status recorded.

so i have this right now:

    for UPDATE
AS 
begin try
INSERT INTO tblHistorySource(*)
select *
from [DELETED]
end try

but i need to shomehow check if tblHistorySource.status not equal to delete.status and only then copy the data....

Check deleted against inserted to see if the values are the same. — HLGEM, Jul 22 '11 at 17:49
Is the trigger on tblHistorySource? If yes then what is the primary key column in table? — TheVillageIdiot, Jul 22 '11 at 17:55

Yuck · Answer 1 · 2011-07-22T19:04:56.513

You'll want to be careful that you're working on a set of data and not just one record at a time:

INSERT INTO tblHistorySource
SELECT *
FROM DELETED INNER JOIN
     INSERTED ON <<DELETED.PK = INSERTED.PK>>
WHERE DELETED.StatusValue <> INSERTED.StatusValue;

The join condition <<DELETED.PK = INSERTED.PK>> will need to be adapted to your schema, as will the real name of StatusValue.

If StatusValue is nullable (thanks Alex), use this instead:

WHERE DELETED.StatusValue <> INSERTED.StatusValue
   OR DELETED.StatusValue IS NULL AND INSERTED.StatusValue IS NOT NULL
   OR DELETED.StatusValue IS NOT NULL AND INSERTED.StatusValue IS NULL;

Which could probably be simplified using an ISNULL() wrapper, but I'd need to know the data type.

If statusValue is nullable, you need to trace changes from NULL to a not NULL values, and vice versa — A-K, Jul 22 '11 at 19:02

JNK · Answer 2 · 2011-07-22T17:57:37.003

1

Just add:

IF ((SELECT Status FROM Delted) <> SELECT Status FROM Inserted))
BEGIN
...
END

edited Jul 22 '11 at 17:57

answered Jul 22 '11 at 17:51

JNK

63,321
15
122
138

are Deleted and Inserted keywords? or am i supposed to put in table names instead? – Madam Zu Zu Jul 22 '11 at 17:52
Deleted and Inserted are result sets generated inside triggers. – JNK Jul 22 '11 at 17:52
Msg 4104, Level 16, State 1, Procedure trg_SourceHistory, Line 5 The multi-part identifier "DELETED.statusCode" could not be bound. – Madam Zu Zu Jul 22 '11 at 17:53

score -2 · Accepted Answer · answered Jul 22 '11 at 17:52

-2

You can do something like this:

DECLARE @statusOldValue status_type
DECLARE @statusNewValue status_type

SELECT @statusOldValue = status FROM deleted

SELECT @statusNewValue= status FROM inserted

IF (@statusOldValue<>@statusNewValue) THEN
BEGIN
 -- Do what you want
END

If you can have situations in which more than one register are updated at the same time then you need to put WHERE clausules to the SELECT statements and put them in iterations in order to treat all the registers.

answered Jul 22 '11 at 17:52

Doliveras

1,794
2
14
30

3

Bad trigger. Do not use. You cannot assume there will be only one record in inserted or deleted. – HLGEM Jul 22 '11 at 18:29
1

This is a very common mistake: you are assuming that only one row can be updated – A-K Jul 22 '11 at 18:59
@HLGEM,As I explained on the text, if you have the possibility of multiple updates on a single query, you have to put all this on a iteration to treat all rows, that way it will work with as many updates as you can have. – Doliveras Jul 25 '11 at 16:40
you never use iteration in trigger. What if someone updated a million rows? – HLGEM Jul 25 '11 at 17:24

Conditional SQL Trigger?

3 Answers3