SQL BEGIN TRANSACTION

Purpose

Flag the beginning of a transaction

Syntax

BEGIN TRANSACTION [<transaction>]

<statements>

END TRANSACTION [<transaction>] BEGIN TRANSACTION [<transaction>]

<statements>

END TRANSACTION [<transaction>]

See Also

COMMIT, END TRANSACTION, ROLLBACK, SAVE TRANSACTION, SAVEPOINT, SET TRANSACTION, TXNISOLATION(), TXNLEVEL()

Description

The BEGIN TRANSACTION statement is used to flag the beginning of a transaction. The END TRANSACTION statement is used to commit changes made during the transaction and close the transaction. The COMMIT statement and the ROLLBACK statement can also be used to close a transaction. The COMMIT statement will save the changes made and the ROLLBACK statement will discard the changes made.

Transactions can be nested by issuing a second or subsequent BEGIN TRANSACTION before an existing transaction has been closed. The TXNLEVEL() function returns the current transaction nesting level. When a transaction is closed, transactions nested within it are also closed.

Savepoints can be set during a transaction. These identify stages within the transaction which can subsequently be used as ROLLBACK points.

The optional <transaction> is a name for the transaction. This name can be used by the COMMIT and ROLLBACK statements.

Example

BEGIN TRANSACTION trans1
INSERT INTO customer;
  (TITLE, LAST_NAME, FIRST_NAME, INITIAL, STREET,;
  CITY, STATE, ZIP,LIMIT, START_DATE);
  VALUES;
  ('Ms', 'Jones', 'Susan', 'B', '177 High Street','Beverly', 'MA', '01915', 2000, date())
INSERT INTO accounts (ORD_VALUE) VALUES (30)
BEGIN TRANSACTION trans2
INSERT INTO accounts (ORD_VALUE) VALUES (60)
// Rollback the trans1 transaction and any transactions
// nested in trans1
ROLLBACK TRANSACTION trans1
END TRANSACTION

Products

Recital Server, Recital