MS SQL Server Interview Question:

How To Access the Inserted Record of an Event?

Submitted by: Administrator

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, ggl_users:

USE GlobalGuideLineDatabase;
GO

DISABLE TRIGGER dml_message ON ggl_users;
GO

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

UPDATE ggl_users SET email='smith@GlobalGuideline'
WHERE name = 'John King';
GO
Email changed to smith@GlobalGuideline
(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.
Submitted by: Administrator