SQL/MX 2.x Reference Manual (H06.10+, J06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—544517-008
2-26
Considerations for BEGIN WORK
BEGIN WORK is an SQL/MX extension.
Considerations for BEGIN WORK
Effect on Audited Tables
A user-defined transaction groups together a set of operations on audited tables so
that changes made by the operations can be committed (with the COMMIT statement)
or rolled back (with the ROLLBACK statement) as a unit. That is, the sequence of SQL
statements that make up the transaction either completely executes or has no effect.
Effect on Nonaudited Tables
Transactions do not protect nonaudited tables. The BEGIN WORK statement has no
effect on nonaudited tables.
MXCI Examples of BEGIN WORK
•
Group three separate statements—two INSERT statements and an UPDATE
statement—that update the database within a single transaction:
--- This statement initiates a transaction.
BEGIN WORK;
--- SQL operation complete.
INSERT INTO sales.orders VALUES (125, DATE '1998-03-23',
DATE '1998-03-30', 75, 7654);
--- 1 row(s) inserted.
INSERT INTO sales.odetail VALUES (125, 4102, 25000, 2);
--- 1 row(s) inserted.
UPDATE invent.partloc SET qty_on_hand = qty_on_hand - 2
WHERE partnum = 4102 AND loc_code = 'G45';
--- 1 row(s) updated.
--- This statement ends a transaction.
COMMIT WORK;
--- SQL operation complete.
C Examples of BEGIN WORK
•
Begin a transaction, execute an UPDATE statement, and test SQLSTATE. If the
UPDATE is successful, the database changes are committed. Otherwise, the
database changes are rolled back:
...
char SQLSTATE_OK[6] = "00000";
EXEC SQL BEGIN DECLARE SECTION;
BEGIN WORK