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.