Joining INSERTED and UPDATED tables when PK column changes

Question

Joining INSERTED and UPDATED tables when PK column changes

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Xedni SSCertifiable Points: 6951 More actions · Answer 1

Thanks all. It seem like barring re architecture, the simplest approach would probably be to add an identity to the source table

LinksUp - Friday, February 17, 2017 10:01 AM

Create an identity int in Table A and an int in Table B.
Populate the identity int in Table A
Join the two tables on the primary key and update Table B's int with the Table A's identity value.

Now when the primary key changes in Table A, join on the identity value and update the primary key values. Done.
If there is a batch of Inserts into Table A, just insert the data into Table B including the new identity from Table A. Done.

This would work well with MERGE.

[/quote]

With regard to adding a surrogate key to the other table, I could do that, but that departs a little bit from my original question. It would absolutely make this simpler, but simply having an identity on my source table is sufficient to know each rows before/after state, and then I can apply that update to the target table.

Executive Junior Cowboy Developer, Esq.[/url]

Xedni SSCertifiable Points: 6951 More actions · Answer 2

ScottPletcher - Thursday, February 16, 2017 4:48 PM
You could add a unique identity column without touching the existing primary key, and that identity value would still allow you to join the deleted and inserted tables accurately.

Although I learned a lot from this conversation, ultimately I think this is probably the cleanest solution.

Executive Junior Cowboy Developer, Esq.[/url]

whenriksen SSCarpal Tunnel Points: 4752 More actions · Answer 3

Just for grins I wanted to test the Row Numbering of Inserted and Deleted.
Testing with 1 Million rows, the records were aligned for every row.
I feel comfortable saying this is a safe way of joining Inserted and Deleted when the PK changes. However, I would also recommend retesting after every patch/hotfix.
Here is my test script.


USE tempdb;
IF OBJECT_ID('tempdb..Test') IS NOT NULL
  DROP TABLE dbo.Test;
GO
IF OBJECT_ID('tempdb..Intermediate') IS NOT NULL
  DROP TABLE dbo.Intermediate;
GO

CREATE TABLE dbo.Test (TestID VARCHAR(38));
GO
INSERT INTO dbo.Test
SELECT TOP 1000000
NEWID()
FROM sys.columns AS c
CROSS JOIN sys.columns AS c2;

CREATE TABLE dbo.Intermediate (
                                ITestID VARCHAR(38),
                                IRowSeq INT,
                                DTestID VARCHAR(38),
                                DRowSeq INT
                              ) ON [PRIMARY];
GO
CREATE TRIGGER trg_Test
ON dbo.Test
AFTER UPDATE
AS
BEGIN
WITH cInserted
AS (SELECT    *,
                RowSeq = ROW_NUMBER() OVER (ORDER BY (SELECT    NULL))
      FROM Inserted
     ),
       cDeleted
AS (SELECT    *,
                RowSeq = ROW_NUMBER() OVER (ORDER BY (SELECT    NULL))
      FROM Deleted
     )
INSERT INTO   dbo.Intermediate ( ITestID,
                                   IRowSeq,
                                   DTestID,
                                   DRowSeq
                                 )
SELECT    *
FROM cInserted
    INNER JOIN cDeleted
      ON cInserted.RowSeq = cDeleted.RowSeq;
END;
GO
UPDATE dbo.Test
SET TestID = TestID;
GO
SELECT *
FROM    dbo.Intermediate AS i
WHERE   i.IRowSeq = i.DRowSeq
        AND i.ITestID <> i.DTestID;

Wes
(A solid design is always preferable to a creative workaround)

Sergiy SSC Guru Points: 110206 More actions · Answer 4

Xedni - Wednesday, February 15, 2017 4:57 PM
To make a long story short, my question boils down to this: If a primary key column changes, how can you correctly pair up rows from the trigger tables INSERTED and DELETED, since the fields which uniquely identify each are no longer in sync?

Do not attempt to update values in PK columns.
It's not what happens behind the scene anyway.

Here is the course of actions you need to take:
1. Create #PKmap table and populate it with CurrentPKcol and NewPKcol values.
2. Begin transaction
3. Insert into the "main" table new record(s) with NewPKcol values and the "tail" from the records where CurrentPKcol = Table.PKcol
3. Insert into all "dependent" tables new records which reference NewPKcol and contain the same data as records referencing CurrentPKcol. Table #PKmap will make it easy.
4. Delete from "dependent" tables all records referencing CurrentPKcol.
5. Delete from "main" table the record(s) referencing CurrentPKcol.
6. If you still have not an error on any of the steps - COMMIT transaction, otherwise - ROLLBACK and investigate.

You may wish to make it into a SP which would check for existence of #PKmap and do all these steps if it's not empty.

_____________
Code for TallyGenerator