SQL Server FAQ - "INSERTED" - New Record of an DML Event Instance

Home >> FAQs/Tutorials >> SQL Server FAQ

By: FYIcenter.com

How To Access the Inserted Record of an Event?

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

A copy of the inserted row for an INSERT statement.
A copy of the updated row for an UPDATE statement.
Empty for a DELETE statement.

The tutorial exercise below shows you how to create a trigger, update_user, to report email changes on table, fyi_users:

USE FyiCenterData;
GO

DISABLE TRIGGER dml_message ON fyi_users;
GO

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

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

As you can see, the INSERTED table is helpful, if you want the trigger to perform specific logics on the affected rows.

(Continued on next topic...)