0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
humbeard
  • 23
  • 3
  • Can you not set a 'where' clause so the inserts only run when your criteria are met? – levelonehuman Dec 20 '17 at 20:36
  • 2
    `IF (select serviceman from deleted) <> (select ServiceMan from inserted)` won't work, try to update >1 rows – Ilyes Dec 20 '17 at 20:37
  • 1
    This will never work like this. When in a delete trigger there is nothing in inserted. And when in a insert trigger there is nothing in inserted. Those tables are only both populated in an update trigger. You stated that you delete everything and then insert everything. This is two statements and you can't capture them the way you are trying in a trigger like this. – Sean Lange Dec 20 '17 at 20:39
  • 3
    I would see about fixing the program so it does an update. Dumping all the data and reinserting is not a good design for managing data. – Sean Lange Dec 20 '17 at 20:40
  • It seems like you are looking for joining the two tables (inserted and deleted) where col <> col then insert those data to your table. – Ilyes Dec 20 '17 at 20:45
  • @Sami but both of those tables are not populated in either a delete or insert trigger. And the OP states they do this as two statements. – Sean Lange Dec 20 '17 at 20:50
  • @SeanLange Yup, but I mentioned that what it looks like the OP trying to do. After that he will face another issue to mention which is the state inserted or deleted – Ilyes Dec 20 '17 at 20:55

0 Answers0