ALLBASE/SQL Reference Manual (36216-90216)

Chapter 10 379
SQL Statements A - D
DELETE
• The search condition is effectively executed for each row of the table or view before any
row is deleted. If the search condition contains a subquery, each subquery in the search
condition is effectively executed for each row of the table or view and the results used in
the application of the search condition to the given row. If any executed subquery
contains an outer reference to a column of the table or view, the reference is to the value
of that column in the given row.
• A deletion from a table with a primary key (a referenced unique constraint) fails 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. It is limited in that a
LONG column cannot be used in the WHERE clause. 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 check constraint search condition defined on a table never prevents a row from being
deleted, whether or not constraint checking is deferred.
• A rule defined with a
StatementType of DELETE will affect DELETE statements
performed on the rule's target table. When the DELETE is performed, each rule defined
on that operation for the table is considered. If the rule has no condition, it will fire for
all rows affected by the statement and invoke its associated procedure with the
specified parameters on each row. If the rule has a condition, it will evaluate the
condition on each row. The rule will fire on rows for which the condition evaluates to
TRUE and invoke the associated procedure with the specified parameters for each 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 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 statements, not for those
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 statement, regardless of the use of
OldCorrelationName , TableName , or NewCorrelationName in the rule definition.
• The set of rows to be affected by the DELETE statement is determined before any rule
fires, and this set remains fixed until the completion of the rule. If the rule adds to,
deletes from, or modifies this set, such changes are ignored.
• 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 and all previous rows. 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