MS SQL Server Interview Question:

How To Access the Deleted Record of an Event?

Submitted by: Administrator

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.
Submitted by: Administrator