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 appears only 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 or deleted pseudo tables (this
example uses the same table as the previous example).
declare curs1 cursor for select * from t12view go open curs1 go fetch curs1 c1 c2 _________ ________ 1 2 (1 row affected) set showplan on go update t12view set c1 = 3 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 (VA = 2) | | |INSTEAD-OF TRIGGER Operator (VA = 1) | | Using Worktable1 for internal storage. | | Using Worktable2 for internal storage. | | | | |CURSOR SCAN Operator (VA = 0) | | | FROM EMIT OPERATOR
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 (VA = 1) | | |SCAN Operator (VA = 0) | | FROM CACHE
The showplan statement above is for the trigger’s
statement. It is identical to the output in the INSTEAD-OF
TRIGGER example.
