"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.

 

"DELETED" - Old Record of an DML Event Instance in SQL Server

sys.trigger_events - Event List of an Existing Trigger in SQL Server

Creating and Managing Triggers in SQL Server

⇑⇑ SQL Server Database Tutorials

2016-10-24, 1332🔥, 0💬