Fire Trigger if deleted.Column <> inserted.Column

609 Views Asked by At

This is similar to Compare deleted and inserted table in SQL Server 2008 but the results were not what I was looking for.

I do want the trigger to fire if a specific column changes, however the program we have does a delete of all information and an reinsert of all new information every time it is saved. there is no simple update.

I want to write to an audit table, but ONLY if that specific column has changed once a save has occurred.

ALTER TRIGGER [dbo].[deleted] 
ON [dbo].[DispTech]           
FOR DELETE, INSERT
AS
    SET NOCOUNT ON;

    IF (SELECT serviceman FROM deleted) <> (SELECT ServiceMan FROM inserted)
    BEGIN
        INSERT INTO misc.dbo.DeletedTest ("Status", dispatch, serviceman)     
            SELECT
                'Deleted', d.Dispatch, d.ServiceMan 
            FROM
                deleted d

       INSERT INTO misc.dbo.DeletedTest ("Status", dispatch, serviceman) 
           SELECT
               'Inserted', i.Dispatch, i.ServiceMan 
           FROM
               inserted i
END 

This does NOT work as it results back NULL for everything. I know I could sort it all out in the audit table if I dump everything in there each time, but I really want a cleaner set of data and want to use it for other processing.

0

There are 0 best solutions below