MS SQL Server Concepts and Programming Question:

Download Job Interview Questions and Answers PDF

How To Access the Inserted Record of an Event?

MS SQL Server Interview Question
MS SQL Server Interview Question

Answer:

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.

Download MS SQL Server Interview Questions And Answers PDF

Previous QuestionNext Question
How To See the Event List of an Existing Trigger using sys.trigger_events?How To Access the Deleted Record of an Event?