Search code examples
sqlsql-serversql-server-2008sqltransaction

Conditional SQL Trigger?

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....

Solution

  • 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.