Using the inserted and deleted Tables

Two special tables are used in trigger statements: the deleted table and the inserted table. Microsoft® SQL Server™ 2000 automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for trigger actions; however, you cannot alter the data in the tables directly.

The inserted and deleted tables are used primarily in triggers to:

• Extend referential integrity between tables.
  
  
• Insert or update data in base tables underlying a view.
  
  
• Check for errors and take action based on the error.
  
  
• Find the difference between the state of a table before and after a data modification and take action(s) based on that difference.

The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table. The deleted table and the trigger table ordinarily have no rows in common.

The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added simultaneously to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.

An update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table.

When you set trigger conditions, use the inserted and deleted tables appropriately for the action that fired the trigger. Although referencing the deleted table while testing an INSERT, or the inserted table while testing a DELETE does not cause any errors, these trigger test tables do not contain any rows in these cases.

Note  If trigger actions depend on the number of rows a data modification effects, use tests (such as an examination of @@ROWCOUNT) for multirow data modifications (an INSERT, DELETE, or UPDATE based on a SELECT statement), and take appropriate actions.

SQL Server 2000 does not allow text, ntext, or image column references in the inserted and deleted tables for AFTER triggers; however, these column references are allowed for INSTEAD OF triggers. For more information, see CREATE TRIGGER.

Using the inserted and deleted Tables in INSTEAD OF Triggers

The inserted and deleted tables passed to INSTEAD OF triggers defined on tables follow the same rules as the inserted and deleted tables passed to AFTER triggers. The format of the inserted and deleted tables is the same as the format of the table on which the INSTEAD OF trigger is defined. Each column in the inserted and deleted tables maps directly to a column in the base table.

The rules regarding when an INSERT or UPDATE statement referencing a table with an INSTEAD OF trigger must supply values for columns are the same as if the table did not have an INSTEAD OF trigger:

• Values cannot be specified for computed columns or columns with a timestamp data type.
  
  
• Values cannot be specified for columns with an IDENTITY property, unless IDENTITY_INSERT is ON for that column. When IDENTITY_INSERT is ON, INSERT statements must supply a value.
  
  
• INSERT statements must supply values for all NOT NULL columns that do not have DEFAULT constraints.
  
  
• For any columns except computed, identity, or timestamp columns, values are optional for any column that allows nulls, or any NOT NULL column that has a DEFAULT definition.

When an INSERT, UPDATE, or DELETE statement references a view that has an INSTEAD OF trigger, the database engine calls the trigger instead of taking any direct action against any table. The trigger must use the information presented in the inserted and deleted tables to build any statements needed to implement the requested action in the base tables even when the format of the information in the inserted and deleted tables built for the view is different than the format of the data in the base tables.

The format of the inserted and deleted tables passed to an INSTEAD OF trigger defined on a view matches the select list of the SELECT statement defined for the view. For example:

CREATE VIEW EmployeeNames (EmployeeID, LName, FName)
AS
SELECT EmployeeID, LastName, FirstName
FROM Northwind.dbo.Employees

The result set for this view has three columns: an int column and two nvarchar columns. The inserted and deleted tables passed to an INSTEAD OF trigger defined on the view also have an int column named EmployeeID, an nvarchar column named LName, and an nvarchar column named FName.

The select list of a view can also contain expressions that do not map directly to a single base table column. Some view expressions, such as a constant or function invocation, may not reference any columns and can be ignored. Complex expressions can reference multiple columns, yet the inserted and deleted tables have only one value for each inserted row. The same issues apply to simple expressions in a view if they reference a computed column that has a complex expression. An INSTEAD OF trigger on the view must handle these types of expressions. For more information, see Expressions and Computed Columns in INSTEAD OF Triggers on Views.