I have a table trigger on UPDATE and INSERT that adds a row to another table. It only needs to add a row if one of four columns are changed. I tried using IF UPDATE(col) to test for changes but it has a blind spot. It only tests that some value came in. I need to go deeper, I need to compare the old and new values to see of a true change has occurred. It has to work with both INSERT and UPDATE.
In the case of an UPDATE that's easy because both the inserted and deleted tables have values I can compare within the trigger. However, for the INSERT only the insert table has values. Because I need this all in the same trigger, how do I handle that INSERT case?
Here is the script of the trigger I want to modify:
ALTER TRIGGER [dbo].[trATPerson_alter] ON [mydb].[dbo].[AT_Person] AFTER INSERT,UPDATE AS BEGIN SET NOCOUNT ON; -- Not all updates require a push IF (UPDATE([First_Name]) OR UPDATE([Last_Name]) OR UPDATE([JobCode]) OR UPDATE([Inactive])) BEGIN INSERT INTO [mydb].[dbo].[AT_Person_To_Push] ( [Facility], [VendorID], [Person_code], [First_Name], [Last_Name], [JobCode], [Alink], [Inactive] ) SELECT [Facility], [VendorID], [Person_code], [First_Name], [Last_Name], [JobCode], [Alink], [Inactive] FROM inserted END END