How To Access the Deleted Record of an Event in SQL Server?

When a DML event occurs, SQL Server will prepare a temporary table called "DELETED", which contains the old record of the affected row, which is:

• A copy of the deleted row for a DELETE statement.
• A copy of the row to be updated for an UPDATE statement.
• Empty for an INSERT statement.

The tutorial exercise below shows you how to improve the trigger, update_user, to report email changes on table, fyi_users, with both old and new emails:

USE FyiCenterData;
GO

ALTER TRIGGER update_user ON fyi_users
AFTER UPDATE
AS
  DECLARE @new VARCHAR(80);
  DECLARE @old VARCHAR(80);
  SELECT @new = email FROM INSERTED;
  SELECT @old = email FROM DELETED;
  PRINT 'Email changed from '+@old+' to '+@new;
GO

UPDATE fyi_users SET email='king@fyicenter'
   WHERE name = 'John King';
GO
Email changed from john@fyicenter to king@fyicenter
(1 row(s) affected)

INSERTED and DELETED are working as expected. The reported message is getting better.

⇒Creating and Managing Triggers in SQL Server

⇒⇒SQL Server Database Tutorials