The INSTEAD-OF TRIGGER operator appears only
in query plans for insert, update,
or delete statements on a view that has an instead-of
trigger created upon it. Its function is to create and fill the
inserted and deleted pseudotables that are used in the trigger to
examine the rows that would have been modified by the original insert, update,
or delete query. The only purpose of the query plan
that contains an INSTEAD-OF TRIGGER operator
is to fill the inserted and deleted tables—the actual operation
of the original SQL statement is never attempted on the view referenced
in the statement. Rather, it is up to the trigger to perform the
updates to the view’s underlying tables based on the data available
in the inserted and deleted pseudo tables.
The following is an example of the INSTEAD-OF
TRIGGER operator’s showplan output:
create table t12 (c0 int primary key, c1 int null, c2 int null) go . . . create view t12view as select c1,c2 from t12 go create trigger v12updtrg on t12view instead of update as select * from deleted go update t12view set c1 = 3 QUERY PLAN FOR STATEMENT 1 (at line 1). STEP 1 The type of query is SELECT. 2 operator(s) under root |ROOT:EMIT Operator (VA = 1) | | |INSTEAD-OF TRIGGER Operator | | Using Worktable1 for internal storage. | | Using Worktable2 for internal storage. | | | | |SCAN Operator (VA = 0) | | | FROM TABLE | | | t12 | | | Table Scan. | | | Forward Scan. | | | Positioning at start of table. | | | Using I/O Size 2 Kbytes for data pages. | | | With LRU Buffer Replacement Strategy for data pages.
In this example, the v12updtrig instead-of
trigger is defined on the t12view. The update
to the t12view results in the creation of the INSTEAD-OF
TRIGGER operator. The INSTEAD-OF
TRIGGER operator creates two worktables. Worktable1 and Worktable2 are
used to hold the inserted and deleted rows, respectively. These
worktables are unique in that they persist across statements. Trigger
execution results in the following showplan lines
getting printed.
QUERY PLAN FOR STATEMENT 1 (at line 3). STEP 1 The type of query is SELECT. 1 operator(s) under root |ROOT:EMIT Operator (VA = 1) | | |SCAN Operator (VA = 0) | | FROM CACHE
The showplan statement output above is for the trigger’s statement, select * from deleted. The rows to be deleted from the view were inserted into the “deleted” cache when the initial update statement was executed. Then, the trigger scans the table to report what rows would have been deleted from the t12view view.
