Home >> FAQs/Tutorials >> SQL Server FAQ
SQL Server FAQ - "DELETED" - Old Record of an DML Event Instance
(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:
ALTER TRIGGER update_user ON fyi_users
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;
UPDATE fyi_users SET email='king@fyicenter'
WHERE name = 'John King';
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...)
- What Are Triggers?
- What Are the Basic Features of a Trigger?
- How To Create a Simple Table to Test Triggers?
- How To Create a DML Trigger using CREATE TRIGGER Statements?
- How To Test a DML Trigger?
- How To List All Triggers in the Database with sys.triggers?
- How To Modify Existing Triggers using "ALTER TRIGGER"?
- How To Delete Existing Triggers using "DROP TRIGGER"?
- How To Get the Definition of a Trigger Back?
- How To Disable Triggers using "DISABLE TRIGGER"?
- How To Create a Trigger for INSERT Only?
- How To See the Event List of an Existing Trigger using sys.trigger_events?
- How To Access the Inserted Record of an Event?
- How To Access the Deleted Record of an Event?
- How To Improve the Trigger to Handle NULL Values?
- What Happens to a Trigger with Multiple Affected Rows?
- How To Override DML Statements with Triggers?
- How To Create a DDL Trigger using "CREATE TRIGGER" Statements?
- Can You Roll Back the DDL Statement in a Trigger?
- Can You Create a Logon Trigger in SQL Server 2005 Express Edition?