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

Q

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

✍: FYIcenter.com

A

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.

 

Creating and Managing Triggers in SQL Server

⇒⇒SQL Server Database Tutorials

2016-10-24, 609👍, 0💬