HP 3000 Manuals HP 3000 Manuals
DELETE WHERE CURRENT
The DELETE WHERE CURRENT statement deletes the current row of an active
set. The current row is the row pointed to by a cursor after the FETCH
or REFETCH statement is issued.
Scope
Application Programs
SQL Syntax
DELETE FROM {[Owner.]TableName} WHERE CURRENT OF CursorName
{[Owner.]ViewName }
Parameters
[Owner.]TableName designates the table from which you are deleting
a row.
[Owner.]ViewName designates a view based on a single table.
ALLBASE/SQL finds the row of the base table
corresponding to the row of the view indicated by
the cursor, and deletes the row from the base
table. Refer to the CREATE VIEW statement for
restrictions governing modifications via a view.
CursorName specifies the name of a cursor. The cursor must
be open and positioned on a row of the table.
The DELETE WHERE CURRENT statement deletes this
row, leaving the cursor with no current row.
(The cursor is said to be positioned between the
preceding and following rows of the active set).
You cannot use the cursor for further updates or
deletions until you reposition it using a FETCH
statement, or until you close and reopen the
cursor.
Description
* 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.
Authorization
If you specify the name of a table, you must have DELETE or OWNER
authority for that table or you must have DBA authority.
If you specify the name of a view, you must have DELETE or OWNER
authority for that view or you must have DBA authority. Also, the owner
of the view must have DELETE or OWNER authority with respect to the
view's base tables, or the owner must have DBA authority.
Example
The active set of this cursor will contain values for the OrderNumber
stored in :OrdNum.
DECLARE DeleteItemsCursor CURSOR FOR
SELECT ItemNumber,OrderQty FROM PurchDB.OrderItems
WHERE OrderNumber = :OrdNum
Statements setting up a FETCH-DELETE WHERE CURRENT loop appear here.
OPEN DeleteItemsCursor
Statements for displaying values and requesting whether the user wants to
delete the associated row go here.
FETCH DeleteItemsCursor INTO :Lin :Linnul, :Orq :Orqnul
DELETE FROM PurchDB.OrderItems WHERE CURRENT OF DeleteItemsCursor
:
CLOSE DeleteItemsCursor