0

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]
  • How would you do it if the PK was a single column? – ypercubeᵀᴹ Jul 12 at 14:11
  • I suppose (UserId, DeviceId, PackageNumber) is unique...??? – McNets Jul 12 at 14:14
  • @ypercubeᵀᴹ If the PK was a single column, I could just do an INNER JOIN on that. Done this already in another trigger and that works flawlessly. – Tom_W Jul 12 at 14:22
  • You can join using 3 columns, just as easily. – ypercubeᵀᴹ Jul 12 at 14:23
  • @McNets As a combination, all three columns are unique. Individually, they will contain delicate data. – Tom_W Jul 12 at 14:24
2
 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.