Searched and Positioned update and delete

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.

Client Cursors

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 SAP ASE 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 SAP ASE until the buffered rows are all read. By default, SAP ASE 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 SAP ASE 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 SAP ASE and in the application: SAP ASE 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 SAP ASE. Because SAP ASE 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 SAP ASE 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 SAP ASE supports.