SQL Trigger - update - add date to date field when status field changes to value 2
I want to set up a trigger on a table so that when the status field is changed to 2 (will be from either 0 or 1) that the trigger inserts the date the status was set to 2 in a seperate field say StatusUpdatedDate. I do not want this updated if another field on this record is update so carn't just check the field value = 2 and then update date with getdate.
Would like to read the status of the record before it is updated to compare. I can get the value of the inserted record but not sure how to get the value of the record before updated - I guess SQL deletes and inserts?.
if deleted.status < 2 and inserted.status = 2