The CURSOR SCAN operator only appears in positioned DELETE or UPDATE (that is, DELETE view-name where current of cursor-name) statements on a view that has an instead-of trigger created upon it. As such, it only appears as a child operator of the INSTEAD-OF TRIGGER operator. A positioned DELETE or UPDATE accesses only the row on which the cursor is currently positioned. The CURSOR SCAN operator reads the current row of the cursor directly from the EMIT operator of the query plan for the “fetch cursor” statement. These values are passed to the INSTEAD-OF TRIGGER operator to be inserted into the inserted and/or deleted pseudo tables.
1> declare curs1 cursor for select * from t12view 1> open curs1 1> fetch curs1 c1 c2 _________ ________ 1 2 (1 row affected) 1> set showplan on 1> update t12view set c1 = 3 2> where current of curs1 QUERY PLAN FOR STATEMENT (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. | | | | |CURSOR SCAN Operator | | | FROM EMIT OPERATOR
Note that the showplan output in this example is identical to that from the previous INSTEAD-OF TRIGGER operator example, with one exception. A CURSOR SCAN operator appears as the child operator of the INSTEAD-OF TRIGGER operator rather than a scan of the view’s underlying tables.
The CURSOR SCAN gets the values to be inserted into the pseudo tables by accessing the result of the cursor fetch. This is conveyed by the “FROM EMIT OPERATOR” message.
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 above is for the trigger’s statement. It is identical to the output in the INSTEAD-OF TRIGGER example.
