Inserted Table and deleted table in the trigger

Abstract]Two special tables are used in the trigger statement: deleted table and inserted Table. Microsoft? SQL Server 2000 automatically creates and manages these tables.
[Keyword] trigger inserted deleted

Two special tables are used in the trigger statement: deleted table and inserted Table. Microsoft? SQL Server 2000 automatically creates and manages these tables. You can use these two temporary tables with resident memory to test the effects of some data modifications and set the trigger operation conditions. However, you cannot directly change the data in the table.

　　The inserted and deleted tables are mainly used in triggers:

· Extended Table reference integrity

· Insert or update data in a base table based on views

· Check for errors and take actions based on Errors

Locate the table status difference before and after data modification, and take action based on the difference.

The Deleted table is used to store copies of rows affected by the DELETE and UPDATE statements. When executing the DELETE or UPDATE statement, the row is deleted from the trigger table and transmitted to the deleted table. The Deleted table and the trigger table do not have the same rows.

The Inserted Table is used to store copies of rows affected by the INSERT and UPDATE statements. In an insert or update transaction, the new row is added 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.

The update transaction is similar to executing Insert after deletion. First, the old row is copied to the deleted table, and then the new row is copied to the trigger table and inserted Table.

When setting trigger conditions, the inserted and deleted tables should be used properly for trigger triggering operations. Although referencing the deleted table during the INSERT test or the inserted Table during the DELETE test will not cause any errors, in this case, the trigger test table will not contain any rows.

Note: If the trigger operation depends on the number of rows affected by a data modification, you should perform a test (such as checking @ rowcount) on multiple rows of data (insert, delete, or update based on select statements ), then take corresponding countermeasures.

SQL Server? 2000 The after trigger is not allowed to reference the text, ntext, or image columns in the inserted and deleted tables. However, the instead of trigger is allowed to reference these columns. For more information, see create trigger.

Use the inserted and deleted tables in the instead of trigger

The inserted and deleted tables passed to the instead of triggers defined on the table follow the same rules as the inserted and deleted tables passed to the After trigger. The formats of the inserted and deleted tables are the same as those of the table on which the instead of trigger is defined. Each column in the inserted and deleted tables is directly mapped to a column in the base table.

When the insert or update statement that references a table with an instead trigger must provide column values is the same as when the table does not have an instead of trigger:

You cannot specify a value for a computed column or a column with a timestamp data type.

The value cannot be specified for a column with the identity attribute, unless the identity_insert of this column is on. When identity_insert is on, the insert statement must provide a value.

The insert statement must provide a value for all not null columns without the default constraint.

For any column except the calculation column, identification column, or timestamp column, the values of any column that allows null values or not null columns with default definitions are optional.

When an INSERT, UPDATE, or DELETE statement references a view with an instead of trigger, the database engine calls the trigger instead of taking any direct operation on any table. Even if the information formats in the inserted and deleted tables generated for the view are different from those in the base table, this trigger generates any statements required to execute the request operations in the base table, you must still use the information in the inserted and deleted tables.

The inserted and deleted table types passed to the instead of trigger defined on the view match the SELECT statement selected for the view. For example:

Create view EmployeeNames (EmployeeID, LName, FName)

AS

SELECT EmployeeID, LastName, FirstName

FROM Northwind. dbo. Employees

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

The view selection list also contains expressions that are not directly mapped to a single base table column. Some view expressions (such as constant calls or function calls) may not reference any column, and such expressions will be ignored. Complex expressions reference multiple columns, but in inserted and deleted tables, each inserted row has only one value. If a simple expression in a view references a computed column with a complex expression, these simple expressions have the same problem. The instead of trigger on the view must process these types OF expressions. For more information, see expressions and calculation columns in the instead of trigger in the view.

By the way, when a trigger is created for a table, it is discussed in three cases.

　　1. Insert)

The Inserted Table has data, and the Deleted table has no data.

　　2. Delete)

The Inserted Table has no data, and the Deleted table has data.

　　3. Update)

The Inserted Table has data (new data), and the Deleted table has data