sqlfriends (7/30/2014)

---

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]

FOR UPDATE

AS

DECLARE @rc AS INT ;

SELECT @rc = COUNT(*)

FROM Deleted ;

IF @rc = 0

BEGIN

RETURN ;

END

ELSE

BEGIN

INSERT INTO dbo.stuPropertyAddressChangeLog

( StudentID ,

AddressID ,

ApartmentNum ,

StartDate,

EndDate,

CreateDt ,

CreatedBy

)

SELECT del.StudentID ,

del.AddressID ,

del.ApartmentNum ,

COALESCE(del.ChangeDt,del.CreateDt),

SYSDATETIME(),

SYSDATETIME(),

COALESCE(del.ChangedBy,del.CreatedBy)

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

AND ISNULL(ins.ApartmentNum,'')=ISNULL(del.ApartmentNum,'')

)

END