Inserted and Deleted Logical Tables

Deleted and inserted tables are logical (conceptual) tables. An inserted table is a pseudo-table containing rows with the inserted values of an insert statement, and a deleted table is a pseudo-table containing rows with the updated values (after image) of an update statement.

The schema of the inserted and deleted tables is identical to that of the view for which the instead of trigger is defined; that is, the view on which a user action is attempted. The difference between these tables and the view is that all the columns of inserted and deleted tables are nullable, even when the corresponding column of the view is not. For example, if the view has a column of datatype char, and an insert statement provides a char value to be inserted, the inserted table has a datatype varchar for the corresponding column, and the input value is converted to varchar. However, trailing blanks are not truncated from the value when the value is added to the inserted table.

When you specify a value of a datatype different from that of the column into which you are inserting it, the value is internally converted to the column datatype. If the conversion succeeds, the converted value is inserted into the table, but if the conversion fails, the statement is aborted. In this example, if a view selects an integer column from a table:

CREATE VIEW v1 AS SELECT intcol FROM t1

The following insert statement causes the instead of trigger on v1 to execute, because the value, 1.0, can be successfully converted to the integer value, 1:

INSERT INTO v1 VALUES (1.0)

However, this next statement causes an exception to be raised, and is aborted before the instead of trigger can execute:

INSERT INTO v1 VALUES (1.1)

The deleted and inserted tables can be examined by the trigger, to determine whether or how trigger action should be carried out, but the tables themselves cannot be altered by trigger action.

The deleted table is used with delete and update; the inserted table, with insert and update.

Note: instead of triggers create the inserted and deleted tables as in-memory tables or worktables. for triggers generate the rows of inserted and deleted tables on the fly, by reading the transactional log, syslogs.

LOB Datatypes

Values for columns in inserted or deleted tables that are large object (LOB) datatypes are stored in memory, and can lead to significant memory usage if there are many rows in these tables, and the rows contain large LOB values.