Conditional SQL Trigger?

i need to create an Update Trigger, th开发者_如何学Cat 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....

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.

Just add:

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

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.