|
|
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.
Application Programs
DELETE FROM {[Owner.]TableName
[Owner.]ViewName} WHERE CURRENT OF CursorName
- [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.
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.
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.
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
|