Delete Trigger - Linking to a table with a PK made up of three columns

by Tom_W   Last Updated July 12, 2019 15:06 PM

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]


Answers 1


 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

db<>fiddle here

McNets
McNets
July 12, 2019 14:34 PM

Related Questions


Trigger to log Users Assigned to website

Updated January 22, 2019 16:06 PM