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
Best Answer
You can handle both INSERT and UPDATE with an EXCEPT set operator. The EXISTS will only evaluate to TRUE both if it's just an INSERT, or if it's an UPDATE with different values for any of these columns.