fishmong3r fishmong3r - 4 months ago 17
SQL Question

SQL trigger to check previous value

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

Answer

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';
Comments