DELETE WHERE CURRENT

This statement cannot be used interactively.

Although the SELECT statement associated with the cursor may specify only some of the columns in a table, the DELETE WHERE CURRENT statement deletes an entire row.

The DELETE WHERE CURRENT statement can be used on an active set associated with a cursor defined using the FOR UPDATE clause.

Do not use this statement in conjunction with rows retrieved using a BULK FETCH.

Using the DELETE statement with the WHERE CURRENT OF CURSOR clause requires that the cursor be defined on the basis of an updatable query. See "Updatability of Queries" in the "SQL Queries" chapter.

The target table of the DELETE WHERE CURRENT statement is specified with TableName or is the base table underlying ViewName. The base table restrictions that govern deletions via cursors are presented in the description of the DECLARE CURSOR statement.

If a referential constraint should be violated during processing of the DELETE statement, the row is not deleted (unless error checking is deferred and the violation is corrected before you COMMIT WORK). Refer to the discussion of the SET CONSTRAINTS statement in this chapter for more information.

A deletion from a table with a primary key (a referenced unique constraint) will fail if any primary key row affected by the DELETE statement is currently referred to by some referencing foreign key row. In order to delete such referenced rows, you must first change the referencing foreign key rows to refer to other primary key rows, to contain a NULL value in one of the foreign key columns, or to delete these referencing rows. Alternatively, you can defer error checking (with the SET CONSTRAINT statement) and fix the error later.

The DELETE syntax is unchanged for use with LONG columns. When LONG data is deleted, the space it occupied in the DBEnvironment is released when your transaction ends. But the physical operating system data file created when you selected the long field earlier still exists and you are responsible for removing it if you desire.

A rule defined with a StatementType of DELETE will affect DELETE WHERE CURRENT statements performed on the rule's target table. When the DELETE WHERE CURRENT is performed, each rule defined on that operation for the table is considered. If the rule has no condition, it will fire and invoke its associated procedure with the specified parameters on the current row. If the rule has a condition, it will evaluate the condition and fire if the condition evaluates to TRUE and invoke the associated procedure with the specified parameters on the current row. Invoking the procedure could cause other rules, and thus other procedures, to be invoked if statements within the procedure trigger other rules.

If a DISABLE RULES statement is in effect, the DELETE WHERE CURRENT statement will not fire any otherwise applicable rules. When a subsequent ENABLE RULES is issued, applicable rules will fire again, but only for subsequent DELETE WHERE CURRENT statements, not for those rows processed when rule firing was disabled.

In a rule defined with a StatementType of DELETE, any column reference in the Condition or any ParameterValue will refer to the value of the column as it exists in the database before it is removed by the DELETE WHERE CURRENT statement, regardless of the use of OldCorrelationName, TableName, or NewCorrelationName in the rule definition.

When a rule is fired by this statement, the rule's procedure is invoked after the changes have been made to the database for that row. The rule's procedure, and any chained rules, will thus see the state of the database with the current partial execution of the statement.

If an error occurs during processing of any rule considered during execution of this statement (including execution of any procedure invoked due to a rule firing), the statement and any procedures invoked by any rules will have no effect. Nothing will have been altered in the DBEnvironment as a result of this statement or the rules it fired. Error messages are returned in the normal way.