You can search or position update and delete statements. A searched delete contains an optional predicate expression in the where clause, that qualifies the rows to be deleted. A searched update contains an optional predicate expression in the where clause, that qualifies the rows to be updated.An example of a searched delete statement is:
DELETE myview WHERE myview.col1 > 5
This statement is executed by examining all the rows of myview, and applying the predicate (myview.col1 > 5 ) specified in the where clause to determine which rows should be deleted.
Joins are not allowed in searched update and delete statements. To use the rows of another table to find the qualifying rows of the view, use a subquery. For example, this statement is not allowed:
DELETE myview FROM myview, mytab where myview.col1 = mytab.col1
But the equivalent statement, using a subquery, is allowed:
DELETE myview WHERE col1 in (SELECT col1 FROM mytab)
Positioned update and delete statements are executed only on the result set of a cursor, and affect only a single row. For example:
DECLARE mycursor CURSOR FOR SELECT * FROM myview OPEN mycursor FETCH mycursor
DELETE myview WHERE CURRENT OF mycursor
The positioned delete statement deletes only the row of myview on which mycursor is currently positioned.
If an instead of trigger exists on a view, it always executes for a qualifying searched delete or update statement; that is, a statement without joins. For an instead of trigger to execute on a positioned delete or update statement, the following two conditions must be met:
The instead of trigger exists when the cursor is declared; that is, when the command declare cursor is executed.
The select statement that defines the cursor can access only the view; for example, the select statement contains no joins, but it can access any subset of the view columns.
The instead of trigger also executes when positioned delete or update statements are executed against scrollable cursors. However, instead of triggers do not fire in one case, when using a client cursor and the command set cursor rows.
A client cursor is declared and fetched in an application using the Open Client library functions for cursor processing. The Open Client library functions can retrieve multiple rows from Adaptive Server in a single fetch command, and buffer these rows, returning one row at a time to the application on subsequent fetch commands, without having to retrieve any more rows from Adaptive Server until the buffered rows are all read. By default, Adaptive Server returns a single row to the Open Client library functions for each fetch command it receives. However, the command set cursor rows can change the number of rows Adaptive Server returns.
Positioned update and delete statements for client cursors, for which set cursor rows is not used to increase the number of rows returned per fetch, cause an instead of trigger to execute. However, if set cursor rows increases the number of rows returned per fetch command, an instead of trigger executes only if the cursor is not marked read-only during the internal processing of declare cursor. For example:
--Create a view that is read-only (without an instead --of trigger) because it uses DISTINCT. CREATE VIEW myview AS SELECT DISTINCT (col1) FROM tab1 --Create an INSTEAD OF DELETE trigger on the view. CREATE TRIGGER mydeltrig ON myview INSTEAD OF DELETE AS BEGIN DELETE tab1 WHERE col1 in (SELECT col1 FROM deleted) END Declare a cursor to read the rows of the view DECLARE cursor1 CURSOR FOR SELECT * FROM myview OPEN cursor1 FETCH cursor1 --The following positioned DELETE statement will --cause the INSTEAD OF TRIGGER, mydeltrig, to fire. DELETE myview WHERE CURRENT OF cursor1 --Change the number of rows returned by ASE for --each FETCH. SET CURSOR ROWS 10 FOR cursor1 FETCH cursor1 --The following positioned DELETE will generate an --exception with error 7732: "The UPDATE/DELETE WHERE --CURRENT OF failed for cursor 'cursor1' because --the cursor is read-only." DELETE myview WHERE CURRENT OF cursor1
Using set cursor rows creates a disconnect
between the position of the cursor in Adaptive Server and in the
application: Adaptive Server is positioned on the last row of the
10 rows returned, but the application can be positioned on any one
of the 10 rows, since the Open Client library functions buffer the
rows and scroll through them without sending information to Adaptive
Server. Because Adaptive Server cannot determine the position of
cursor1 when the positioned delete statement
is sent by the application, the Open Client library functions also
send the values of a subset of the columns of the row where cursor1
is positioned in the application. These values are used to convert
the positioned delete into
a searched delete statement. This means that
if the value of col1 is 5 in the current row
of cursor1, a clause such as 'where
col1 = 5' is used by Adaptive Server
to find the row.
When a positioned delete is converted to a searched delete, the cursor must be updatable, just as for cursors on tables and on views without instead of triggers. In the example above, the select statement that defines cursor1 is replaced by the select statement that defines myview:
DECLARE cursor1 CURSOR FOR SELECT * FROM myview
becomes:
DECLARE cursor1 CURSOR FOR SELECT DISTINCT (col1) FROM tab1
Because of the distinct option in the select list, cursor1 is not updatable; in other words, it is read-only. This leads to the 7732 error when the positioned delete is processed.
If the cursor that results from replacing the view by its defining select statement is updatable, the instead of trigger fires, whether set cursor rows is used or not. Using set cursor rows does not prevent an instead of trigger from firing in other types of cursors Adaptive Server supports.
