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
;

• udhayan dharmalingam: Thank you so much @Charlieface - Keycol is unique clustered index and I was under the assumption that the update/delete trigger will fail if it cannot find that record in target table (similar to how it works in transactional replication). I will test the first script. I think that suits my requirement.
• udhayan dharmalingam: About the merge approach, I dont want the merge to insert if the record is not yet in the newtable because if it inserts it before my import job attempts to insert it, my import job will fail with key violation error. new records that gets inserted after my import start date will be handled by the insert trigger.
• Charlieface: Good point. What do you want to happen, do you want to just insert that new row? The Merge version will just insert it, but the Insert/Update/Delete version will get an error if the row exists already
• Charlieface: Good point, forgot that, was too caught up in OP's original code. Maybe you should write that as an answer rather than a comment...........
• udhayan dharmalingam: My requirement is if the record is not present in the new table, update/delete trigger should silently fend without doing anything. This means the actual record which got updated in the current table will eventually imported into new table by the batch job

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.