Difference between revisions of "SQL BEGIN TRANSACTION"

From Recital Documentation Wiki
Jump to: navigation, search
 
Line 33: Line 33:
 
<code lang="recital">
 
<code lang="recital">
 
BEGIN TRANSACTION trans1
 
BEGIN TRANSACTION trans1
INSERT INTO customer;
+
  INSERT INTO customer;
  (TITLE, LAST_NAME, FIRST_NAME, INITIAL, STREET,;
+
    (TITLE, LAST_NAME, FIRST_NAME, INITIAL, STREET,;
  CITY, STATE, ZIP,LIMIT, START_DATE);
+
    CITY, STATE, ZIP,LIMIT, START_DATE);
  VALUES;
+
    VALUES;
  ('Ms', 'Jones', 'Susan', 'B', '177 High Street','Beverly', 'MA', '01915', 2000, date())
+
    ('Ms', 'Jones', 'Susan', 'B', '177 High Street','Beverly', 'MA', '01915', 2000, date())
INSERT INTO accounts (ORD_VALUE) VALUES (30)
+
  INSERT INTO accounts (ORD_VALUE) VALUES (30)
BEGIN TRANSACTION trans2
+
  BEGIN TRANSACTION trans2
INSERT INTO accounts (ORD_VALUE) VALUES (60)
+
  INSERT INTO accounts (ORD_VALUE) VALUES (60)
// Rollback the trans1 transaction and any transactions
+
  // Rollback the trans1 transaction and any transactions
// nested in trans1
+
  // nested in trans1
ROLLBACK TRANSACTION trans1
+
  ROLLBACK TRANSACTION trans1
 
END TRANSACTION
 
END TRANSACTION
 
</code>
 
</code>

Latest revision as of 15:31, 22 December 2009

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