MS SQL Server Concepts and Programming Question:

Download Job Interview Questions and Answers PDF

How To Access the Deleted 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 "DELETED", which contains the old record of the affected row, which is:

* A copy of the deleted row for a DELETE statement.
* A copy of the row to be updated for an UPDATE statement.
* Empty for an INSERT statement.

The tutorial exercise below shows you how to improve the trigger, update_user, to report email changes on table, ggl_users, with both old and new emails:

USE GlobalGuideLineDatabase;
GO

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

UPDATE ggl_users SET email='king@ggl'
WHERE name = 'John King';
GO
Email changed from smith@GlobalGuideline to master@GlobalGuideline
(1 row(s) affected)

INSERTED and DELETED are working as expected. The reported message is getting better.

Download MS SQL Server Interview Questions And Answers PDF

Previous QuestionNext Question
How To Access the Inserted Record of an Event?What Happens to a Trigger with Multiple Affected Rows?