How is a T-SQL Trigger that only fires on real changes made?
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.
IF EXISTS (
SELECT First_Name, Last_Name, JobCoe, Inactive FROM inserted
EXCEPT
SELECT First_Name, Last_Name, JobCoe, Inactive FROM deleted
)
BEGIN...
In case an update can affect multiple rows, you have to protect against two things:
- We want to consider updates that swap values between similar rows. If there are two John Smiths who need their JobCodes updated (first John from 1 to 2; second John from 2 to 1), we need to be careful to say they both were updated.
- We only want to log the changed rows in
AT_Person_To_Push
. If 5 rows are updated, but only 2 are updated in a way that we care about, then we need to process only the 2 relevant rows.
Here's how I would handle it:
- Left join
inserted
todeleted
, becauseinserted
will have rows for inserts and updates whiledeleted
will only have rows for updates. - Use
EXISTS
withEXCEPT
to find rows where theinserted
values differ from thedeleted
values. You can't usei.First_Name != d.First_Name OR i.Last_Name != d.Last_Name...
because the deleted table will be empty (and the LEFT JOIN will return nulls) when the trigger is handling an INSERT. - Insert only the affected rows into
AT_Person_To_Push
.
ALTER TRIGGER [dbo].[trATPerson_alter]
ON [mydb].[dbo].[AT_Person]
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [mydb].[dbo].[AT_Person_To_Push] (
[Facility],
[VendorID],
[Person_code],
[First_Name],
[Last_Name],
[JobCode],
[Alink],
[Inactive]
)
SELECT i.[Facility],
i.[VendorID],
i.[Person_code],
i.[First_Name],
i.[Last_Name],
i.[JobCode],
i.[Alink],
i.[Inactive]
FROM inserted i
LEFT JOIN deleted d
ON i.Person_code = d.Person_code
-- Check for changes that require a push
WHERE EXISTS (SELECT i.[First_Name], i.[Last_Name], i.[JobCode], i.[Inactive]
EXCEPT
SELECT d.[First_Name], d.[Last_Name], d.[JobCode], d.[Inactive]);
END