I'm currently writing a number of "AFTER INSERT, DELETE" triggers that will be used to sync data between two identical tables, in different instances. The issue that I'm having right now, is getting the DELETE part working, due to how the table has been designed.
The table is called "MobileUsageReportPackageLog" and the PK is made up of the following columns, UserId, DeviceId, PackageNumber.
I'm now unsure how to link this table to the "deleted" table, to ensure that if a record is deleted, then the trigger also deletes the same record in the other table.
I have a feeling that I'm missing something simple, but if anyone has any suggestions or recommendations for a situation like this, I'd love to hear them.
Thanks in advance!
EDIT: I should mention that as a combination, all three column will be unique. As individual columns however, they will contain duplicate data.
Code as of now:
IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) BEGIN DELETE murpl FROM [Audits].[dbo].[MobileUsageReportPackageLog] murpl JOIN deleted del ON murpl.[UserId] = del.[UserId] JOIN deleted del2 ON murpl.DeviceId = del2.[DeviceId] JOIN deleted del3 ON murpl.[PackageNumber] = del3.[PackageNumber]