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]
CREATE TABLE A ( ID1 INT, ID2 INT, ID3 INT, FOO INT, BAR INT, PRIMARY KEY (ID1, ID2, ID3) ); CREATE TABLE B ( ID1 INT, ID2 INT, ID3 INT, FOO INT, BAR INT, PRIMARY KEY (ID1, ID2, ID3) ); GO
CREATE TRIGGER TRG_A_INSDEL ON [A] AFTER INSERT, DELETE AS BEGIN /* Insert new rows */ INSERT INTO B SELECT * FROM inserted; /* Delete those rows that match PK */ DELETE B FROM B JOIN deleted d ON d.ID1 = B.ID1 AND d.ID2 = B.ID2 AND d.ID2 = B.ID2; END GO
INSERT INTO A VALUES (1, 1, 1, 1, 1); INSERT INTO A VALUES (2, 2, 2, 2, 2); INSERT INTO A VALUES (3, 3, 3, 3, 3); GO
SELECT * FROM B; GO ID1 | ID2 | ID3 | FOO | BAR --: | --: | --: | --: | --: 1 | 1 | 1 | 1 | 1 2 | 2 | 2 | 2 | 2 3 | 3 | 3 | 3 | 3
DELETE FROM A WHERE ID1 >= 2; GO
SELECT * FROM B; GO ID1 | ID2 | ID3 | FOO | BAR --: | --: | --: | --: | --: 1 | 1 | 1 | 1 | 1