 |
» |
|
|
|
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.
At COMMIT 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.
At COMMIT WORK, work done by statements within the transaction that executed
without error will be written to the DBEnvironment.
Within statements which generated errors at a specific row,
work done on rows prior to the row generating the error
will be written to the DBEnvironment;
no work will be done from the erroneous row, forward.
Unless you have a severe error (4008, 4009, or -14024 or greater),
the transaction is not rolled back,
and previous statements within the transaction are still in effect.
When a transaction ends, DML ATOMICITY
remains at or is returned to STATEMENT level.
The SET DML ATOMICITY statement is sensitive to savepoints. If you
establish a save point, then change the atomicity level, and then roll
back to the savepoint, the atomicity level set after the savepoint will
be undone.
If DML ATOMICITY is set at ROW and you set it to ROW again, a warning
message is issued. If DML ATOMICITY is set at STATEMENT and you set it
to STATEMENT again, a warning message is issued.
DML ATOMICITY does not apply to DDL statements. DDL statements are always
checked at statement level.
DML ATOMICITY does not apply to statements that may fire rules. Such
statements are always checked at statement level.
When the SET CONSTRAINTS statement sets constraint error checking to IMMEDIATE,
constraint error checking will be performed at the level set by the most
recent SET DML ATOMICITY statement.
Refer to the SET CONSTRAINTS statement for more information.
Authorization | |
Anyone can use the SET DML ATOMICITY statement.
Example | |
The user wants to load supposedly error-free data into PurchDB.Parts.
Immediately after DBEnvironment creation, when initially loading the tables
while non-archive mode logging is in effect, performance can be improved
if you SET DML ATOMICITY to ROW LEVEL. However, if an error is encountered,
the insertion of rows prior to the erroneous row will not be rolled back.
Error checking is set at row level.
SET DML ATOMICITY AT ROW LEVEL
|
The rows to be inserted are in the array called PartsArray.
BULK INSERT INTO PurchDB.Parts
VALUES (:PartsArray, :StartIndex, :NRows)
|
You can set the level back to statement level before the transaction ends.
.
.
.
Other statements are listed here.
.
.
.
COMMIT WORK
|
If you have not already set error checking back to statement level, it is
automatically set back to statement level when the transaction ends.
|
