SQL Server › Triggers

How do you access the old and new values of a row that was just updated from inside a trigger?

Intermediate

Answer

Inside a trigger, SQL Server automatically provides two special pseudo tables, called inserted and deleted, where the deleted table contains the row's values before the change and the inserted table contains its values after the change, letting you compare exactly what was modified during an update.

CREATE TRIGGER trg_PriceChangeLog
ON Products
AFTER UPDATE
AS
BEGIN
  INSERT INTO PriceHistory (ProductId, OldPrice, NewPrice)
  SELECT i.ProductId, d.Price, i.Price
  FROM inserted i
  JOIN deleted d ON i.ProductId = d.ProductId
  WHERE i.Price <> d.Price;
END;

Real-world example A product catalog automatically logs a complete price change history by comparing the old and new price values available through the inserted and deleted pseudo tables inside an update trigger.

Common follow-ups: What do the inserted and deleted tables contain during a plain INSERT or DELETE trigger?;Can multiple rows be present in inserted and deleted at the same time?

Change Data Capture (CDC);Data Types & Schema Design