ALLBASE/SQL Reference Manual (36216-90216)

526 Chapter12
SQL Statements S - Z
SET DML ATOMICITY
SET DML ATOMICITY
The SET DML ATOMICITY statement sets the general error checking level in data
manipulation statements.
Scope
ISQL or Application Programs
SQL Syntax
SET DML ATOMICITY AT {ROW
STATEMENT} LEVEL
Parameters
ROW specifies that general error checking occurs at the row level. The term
general error checking refers to any errors, for example, arithmetic
overflows or constraint violation errors.
STATEMENT specifies that general error checking occurs at the statement level. This is
the default general error checking level.
Description
• Constraint errors (UNIQUE, REFERENTIAL, or CHECK constraint violations) are
handled just like any other general error when constraint checking is in IMMEDIATE
mode. In this case, error handling follows the behavior outlined below. However, when
you SET CONSTRAINTS DEFERRED, constraint error checking behaves differently as
described in the SET CONSTRAINTS statement in this chapter. The following discussion
assumes that constraint checking is in IMMEDIATE mode.
• Setting DML ATOMICITY affects the BULK INSERT, DELETE, UPDATE, UPDATE
WHERE CURRENT, DELETE WHERE CURRENT statements, and the ISQL LOAD
command when they operate on a
set of rows.
• When you use SET DML ATOMICITY AT STATEMENT LEVEL (the default), and if an
error occurs:
• Work done by the statement before an error occurs
is undone , and the statement is
no longer in effect.
•AtCOMMIT WORK, work done by statements within the transaction that executed
without error will be written to the DBEnvironment, while statements with errors
will have no effect.
• When you use SET DML ATOMICITY AT ROW LEVEL (not the default), and if an error
occurs:
— Work done by a statement before an error occurs
is not undone , but no further
action is taken by the statement.