I have an address table, and a log table will only record changes in it. So we wrote a after udpate trigger for it. In our case the trigger only need to record historical changes into the log table. so it only needs to be an after update trigger.
I attached the table create script.
The trigger works fine until a day we found out there are same addresses exist in the log table for the same student.
so below is what I modified the trigger to.
I tested, it seems working OK. Also would like to know do I need to use if not exists statement, or just use in the where not exists like what I did in the following code:
ALTER TRIGGER [dbo].[trg_stuPropertyAddressChangeLog] ON [dbo].[stuPropertyAddress]
DECLARE @rc AS INT ;
SELECT @rc = COUNT(*)
FROM Deleted ;
IF @rc = 0
INSERT INTO dbo.stuPropertyAddressChangeLog
( StudentID ,
SELECT del.StudentID ,
FROM deleted del
WHERE NOT EXISTS
( SELECT *
FROM inserted AS ins
JOIN deleted AS del
ON ins.studentid=del.studentid AND ins.AddressID=del.addressid