SQL/MX 3.2.1 Reference Manual (H06.26+, J06.15+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2.1 Reference Manual—691117-005
2-256
Considerations for INSERT
Considerations for INSERT
Starting with SQL/MX Release 3.2, self-referencing inserts are supported. With this
support, you can select the rows to be inserted from the target table in a subquery.
Statement atomicity means that a statement will either complete or be rolled back,
without having to rollback a business transaction that contains multiple statements.
SQL/MX will try to undo any changes to the database as a result of an insert in case a
row cannot be inserted, typically because of a constraint violation such as a duplicate
row.
There are some conditions where such an undo operation will cause an active
transaction to be rolled back instead of just the statemement. The following are some
examples where the active transaction will be rolled back:
•
Parallel inserts performed by ESPs
•
VSBB inserts (either explicitly enforced by the CQD INSERT_VSBB set to ON or
when chosen by the optimizer)
•
CQD UPD_ABORT_ON_ERROR is set to ON to force transactions to be aborted.
This CQD is supported to preserve the behavior of older releases
•
The underlying table has referential integrity constraints or triggers defined
For more information, see Transaction Management on page 1-13.
Use the EXPLAIN statement to check whether transactions will be rolled back or if
statement atomicity will be used. For more information, see EXPLAIN Statement on
page 2-207.
Authorization Requirements
INSERT requires authority to read and write to the table or view receiving the data and
authority to read tables or views specified in the query expression (or any of its
subqueries) in the INSERT statement.
Transaction Initiation and Termination
The INSERT statement will automatically initiate a transaction only if TRANSACTION
AUTOBEGIN is set to ON. If a separate BEGIN WORK was issued, the INSERT
statement operates under that transaction.
The INSERT statement will commit the transaction if TRANSACTION AUTOCOMMIT
is set to ON. If AUTOCOMMIT is set to OFF, you must explicitly commit the
transaction.
If a table is not audited, transactions do not apply.