SQL END TRANSACTION

Purpose

Commit changes made during a transaction and close the transaction

Syntax

BEGIN TRANSACTION [<transaction>]

<statements>

END TRANSACTION [<transaction>]

See Also

BEGIN TRANSACTION, COMMIT, 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