SQL server update trigger to track updates on any column

Question

I am working on a data migration from a current prod table to a new table. I have prepared and tested the batch insert script based on the [Keycol] and [col8] (datetime) . This import is expected to go on for about 10 days.

--old table
SELECT TOP (1000) [Keycol],[col2],[col3],[col4],[col5],[col6],[col7],[col8],[col9],[col10]
FROM [dbo].[oldtable]

--newtable with identity column
SELECT TOP (1000) [NewIdentitycol],[Keycol],[col2],[col3],[col4],[col5],[col6],[col7],[col8],[col9],[col10]
FROM [dbo].[newtable]

I am preparing the insert/update/delete trigger on oldtable to capture any insert/update/delete after I started the import and do the same in my newtable.

I am new to triggers and most of the references I see in Google just tracks any one column for an update. I basically want to write trigger which captures update to any column in the tracked table and if that record is already imported into the new table, apply that update.

I took reference from https://www.sqlshack.com/solve-identity-crisis-sql-server/

Insert looks straight forward, but not sure what I tried in update/delete works for my case. I am going to do the POC, is there a better way of handing this. Please let me know.

CREATE TRIGGER Migration_trigger
ON dbo.oldtable
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    -- INSERTS ONLY
    
    INSERT INTO dbo.newtable
        ([Keycol],[col2],[col3],[col4],[col5],[col6],[col7],[col8],[col9],[col10])
    SELECT Inserted.[Keycol],Inserted.[col2],Inserted.[col3],Inserted.[col4],Inserted.[col5],Inserted.[col6],Inserted.[col7],Inserted.[col8],Inserted.[col9],Inserted.[col10]
    FROM Inserted
    LEFT JOIN Deleted
    ON Deleted.[Keycol] = Inserted.[Keycol]
    WHERE Deleted.[Keycol] IS NULL;
    
 
    -- DELETE ONLY already imported record
    IF(SELECT [Keycol] from dbo.newtable n
    join inserted on n.[keycol] = inserted.[keycol]
    join deleted on inserted.[keycol] = Deleted.[keycol] where n.[Keycol]= Deleted.[Keycol]) IS NOT NULL
    BEGIN
        DELETE 
        FROM dbo.newtable
        WHERE newtable.[Key] IN (
        SELECT Deleted.[Keycol]
        FROM Deleted
        LEFT JOIN Inserted
        ON Deleted.[Keycol] = Inserted.[Keycol]
        WHERE Inserted.[Keycol] IS NULL);
    END

    -- UPDATE ONLY already imported record

        IF(SELECT [Keycol] from dbo.newtable n
    join inserted  on n.[keycol] = inserted.[keycol]
    join deleted on inserted.[keycol] = Deleted.[keycol] where n.[Keycol]= Deleted.[Keycol] and [Keycol]=inserted.[Keycol] ) IS NOT NULL
    BEGIN
        UPDATE dbo.newtable 
        SET [Keycol] = Inserted.[Keycol],[col2]=Inserted.[col2],[col3]=Inserted.[col3],[col4]=Inserted.[col4],[col5]=Inserted.[col5],[col6]=Inserted.[col6],
        [col7]=Inserted.[col7],[col8]=Inserted.[col8],[col9]=Inserted.[col9],[col10]=Inserted.[col10]
        FROM Inserted
        INNER JOIN Deleted
        ON Deleted.[Keycol] = Inserted.[Keycol]
        INNER JOIN dbo.newtable
        ON newtable.[Keycol] = Inserted.[Keycol]
    END
END
     

Thanks

Answer 1

It's almost right, however the IF (SELECT is going to fail if there are multiple rows, and in any case is unnecessary (if there are no rows then nothing will happen).

Furthermore

• Use a WHERE NOT EXISTS instead of LEFT JOIN...IS NULL, it's usually faster.
• Use table aliases, and don't quote names unnecessarily, to make it more readable.
• Bail early if there are no rows.
• In the DELETE you can use EXCEPT to make it more readable.
• In the UPDATE you do not need to update the primary key, as you are joining on that anyway.
  Also do not use the full table name on the first line, only the alias

CREATE OR ALTER TRIGGER Migration_trigger
ON dbo.oldtable
AFTER INSERT, UPDATE, DELETE
AS

IF @@ROWCOUNT = 0
    RETURN;  -- early bail-out

SET NOCOUNT ON;

INSERT INTO dbo.newtable
    (Keycol, col2, col3, col4, col5, col6, col7, col8, col9, col10)
    SELECT i.Keycol, i.col2, i.col3, i.col4, i.col5, i.col6, i.col7, i.col8, i.col9, i.col10
    FROM inserted i
    WHERE NOT EXISTS (SELECT 1
        FROM deleted d
        WHERE d.Keycol = i.Keycol
    );    
 
DELETE 
    FROM dbo.newtable
    WHERE newtable.Key IN (
        SELECT d.Keycol
        FROM deleted d
        EXCEPT
        SELECT i.Keycol
        FROM inserted i
    );

UPDATE n
SET col2 = i.col2,
    col3 = i.col3,
    col4 = i.col4,
    col5 = i.col5,
    col6 = i.col6,
    col7 = i.col7,
    col8 = i.col8,
    col9 = i.col9,
    col10 = i.col10
FROM inserted i
JOIN deleted ON d.Keycol = i.Keycol
JOIN dbo.newtable n ON n.Keycol = i.Keycol;     

You can also use a MERGE

CREATE OR ALTER TRIGGER Migration_trigger
ON dbo.oldtable
AFTER INSERT, UPDATE, DELETE
AS

IF @@ROWCOUNT = 0
    RETURN;  -- early bail-out

SET NOCOUNT ON;

WITH source AS (
    SELECT
      i.*,
      ActualKey = ISNULL(i.Keycol, d.Keycol)
    FROM inserted i
    FULL JOIN deleted d ON d.Keycol = i.Keycol
)
MERGE dbo.newtable AS n
USING source AS i
ON i.ActualKey = n.Keycol
WHEN NOT MATCHED THEN
  INSERT
    (Keycol, col2, col3, col4, col5, col6, col7, col8, col9, col10)
  VALUES (i.Keycol, i.col2, i.col3, i.col4, i.col5, i.col6, i.col7, i.col8, i.col9, i.col10)
WHEN MATCHED AND i.Keycol IS NULL THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET
    col2 = i.col2,
    col3 = i.col3,
    col4 = i.col4,
    col5 = i.col5,
    col6 = i.col6,
    col7 = i.col7,
    col8 = i.col8,
    col9 = i.col9,
    col10 = i.col10
;

Answer 2

You don't need to write the batch transfer and trigger code from scratch. It can be tricky to get everything right, handle error conditions properly and make the whole process resumable.

Michael J Swart has an easy-to-read series starting with Modifying Tables Online – Part 1: Migration Strategy

The general idea is the same as yours:

1. Create a staging table
2. Mirror ongoing changes to the original table using triggers
3. Migrate data in batches
4. Swap the tables at the end

He uses separate triggers for insert, update, and delete in his AdventureWorks example since that makes the code simpler:

/*
create triggers to maintain new table
*/
 
use AdventureWorks2012;
go
 
create trigger t_i_SalesOrderHeader
on Sales.SalesOrderHeader
after insert
as
    set identity_insert Sales.SalesOrderHeader_new on;
 
    insert SalesOrderHeader_new(SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, [Status], OnlineOrderFlag, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, Comment, rowguid, ModifiedDate)
    select SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, [Status], OnlineOrderFlag, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, Comment, rowguid, ModifiedDate
    from inserted
 
    set identity_insert Sales.SalesOrderHeader_new off;
go
 
create trigger t_u_SalesOrderHeader
on Sales.SalesOrderHeader
after update
as
    -- assuming pk values are never altered.
    update Sales.SalesOrderHeader_new
    set
        s.RevisionNumber = i.RevisionNumber,
        s.OrderDate  = i.OrderDate,
        s.DueDate  = i.DueDate,
        s.ShipDate  = i.ShipDate,
        s.[Status]  = i.[Status],
        s.OnlineOrderFlag  = i.OnlineOrderFlag,
        s.PurchaseOrderNumber  = i.PurchaseOrderNumber,
        s.AccountNumber  = i.AccountNumber,
        s.CustomerID  = i.CustomerID,
        s.SalesPersonID  = i.SalesPersonID,
        s.TerritoryID  = i.TerritoryID,
        s.BillToAddressID  = i.BillToAddressID,
        s.ShipToAddressID  = i.ShipToAddressID,
        s.ShipMethodID  = i.ShipMethodID,
        s.CreditCardID  = i.CreditCardID,
        s.CreditCardApprovalCode = i.CreditCardApprovalCode,
        s.CurrencyRateID  = i.CurrencyRateID,
        s.SubTotal  = i.SubTotal,
        s.TaxAmt  = i.TaxAmt,
        s.Freight  = i.Freight,
        s.Comment  = i.Comment,
        s.rowguid  = i.rowguid,
        s.ModifiedDate = i.ModifiedDate
    from Sales.SalesOrderHeader_new s
    join inserted i
        on s.SalesOrderID = i.SalesOrderID;
go
 
create trigger t_d_SalesOrderHeader
on Sales.SalesOrderHeader
after delete
as
    delete Sales.SalesOrderHeader_new
    from Sales.SalesOrderHeader_new s
    join deleted d
        on d.SalesOrderID = s.SalesOrderID ;
go

Small improvements are possible, but the basic implementation is sound as a template to work from. I would add the following to the start of each trigger, for example:

IF ROWCOUNT_BIG() = 0 BEGIN RETURN END;
SET NOCOUNT ON;
SET ROWCOUNT 0;

Make sure you read and understand all five parts of the series. They're quite short.