Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - "DELETED" - Old Record of an DML Event Instance

By: FYIcenter.com

(Continued from previous topic...)

How To Access the Deleted Record of an Event?

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, fyi_users, with both old and new emails:

USE FyiCenterData;
GO

ALTER TRIGGER update_user ON fyi_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 fyi_users SET email='king@fyicenter'
   WHERE name = 'John King';
GO
Email changed from john@fyicenter to king@fyicenter
(1 row(s) affected)

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

(Continued on next topic...)

1. What Are Triggers?
2. What Are the Basic Features of a Trigger?
3. How To Create a Simple Table to Test Triggers?
4. How To Create a DML Trigger using CREATE TRIGGER Statements?
5. How To Test a DML Trigger?
6. How To List All Triggers in the Database with sys.triggers?
7. How To Modify Existing Triggers using "ALTER TRIGGER"?
8. How To Delete Existing Triggers using "DROP TRIGGER"?
9. How To Get the Definition of a Trigger Back?
10. How To Disable Triggers using "DISABLE TRIGGER"?
11. How To Create a Trigger for INSERT Only?
12. How To See the Event List of an Existing Trigger using sys.trigger_events?
13. How To Access the Inserted Record of an Event?
14. How To Access the Deleted Record of an Event?
15. How To Improve the Trigger to Handle NULL Values?
16. What Happens to a Trigger with Multiple Affected Rows?
17. How To Override DML Statements with Triggers?
18. How To Create a DDL Trigger using "CREATE TRIGGER" Statements?
19. Can You Roll Back the DDL Statement in a Trigger?
20. Can You Create a Logon Trigger in SQL Server 2005 Express Edition?