SQL trigger to check previous value

Question

I have a trigger on table tblA if the status is closed it inserts a record to table tblB. The below code is working, but I want it to be executed only if the previous status was NOT closed. How can I check it?

ON tblA
AFTER UPDATE
AS
  BEGIN
    SET NOCOUNT ON
    INSERT INTO tblB 
        (a_id, action, usr)
        select a.ID, 'closed', a.usr
        from tblA a
    WHERE a.Status LIKE 'closed';  
    END

score 4 · Accepted Answer · answered Jul 20 '16 at 09:27

4

A trigger in SQL Server is always suspect if it fails to access either inserted or deleted (or both), since those are the pseudo-tables that give you the details of what changed.

You want something like:

INSERT INTO tblB 
    (a_id, action, usr)
    select a.ID, 'closed', a.usr
    from inserted a
            inner join
         deleted d
            on a.ID = d.ID
WHERE a.Status = 'closed'
  AND d.Status != 'closed';

answered Jul 20 '16 at 09:27

Damien_The_Unbeliever

234,701
27
340
448

So if one would modify a record, then the "old" values are being stored in `deleted` even if I trigger it `AFTER UPDATE`? – fishmong3r Jul 20 '16 at 09:28
1

@fishmong3r - yes, for an update `deleted` contains the old values and `inserted` contains the new values. – Damien_The_Unbeliever Jul 20 '16 at 09:29
That's awesome. Thanks a lot. – fishmong3r Jul 20 '16 at 09:31

SQL trigger to check previous value

1 Answers1