The instead-of trigger operator only appears in query plans for INSERT, DELETE, or UPDATE statements on a view which has an instead-of trigger created upon it. Its function is to create and fill the inserted and deleted pseudo tables that are used in the trigger to examine the rows that would have been modified by the original INSERT, DELETE, or UPDATE query. The only purpose of the query plan that contains the INSTEAD-OF TRIGGER operator is to fill the inserted and deleted tables -- the actual operation of the original SQL statement (INSERT, DELETE, or UPDATE) 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:
1> create table t12 (c0 int primary key, c1 int null, c2 int null) . . . 1> create view t12view as select c1,c2 from t12 1> create trigger v12updtrg on t12view 2> instead of update as 3> select * from deleted 1> 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 | | |INSTEAD-OF TRIGGER Operator | | Using Worktable1 for internal storage. | | Using Worktable2 for internal storage. | | | | |SCAN Operator | | | 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 will 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 | | |SCAN Operator | | 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.
