 |
» |
|
|
|
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 [Owner.] ViewName } WHERE CURRENT OF CursorName 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. 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
|
|
