Difference between revisions of "SQL BEGIN TRANSACTION"

From Recital Documentation Wiki
Jump to: navigation, search
 
(2 intermediate revisions by 2 users not shown)
Line 4: Line 4:
  
 
==Syntax==
 
==Syntax==
 +
BEGIN TRANSACTION [<transaction>]
 +
 +
<statements>
 +
 +
END TRANSACTION [<transaction>]
 
BEGIN TRANSACTION [<transaction>]
 
BEGIN TRANSACTION [<transaction>]
  
Line 27: Line 32:
 
==Example==
 
==Example==
 
<code lang="recital">
 
<code lang="recital">
// config.db
+
BEGIN TRANSACTION trans1
set sql to recital
+
  INSERT INTO customer;
set sql on
+
    (TITLE, LAST_NAME, FIRST_NAME, INITIAL, STREET,;
// end of config.db
+
    CITY, STATE, ZIP,LIMIT, START_DATE);
 
+
    VALUES;
// Transactions
+
    ('Ms', 'Jones', 'Susan', 'B', '177 High Street','Beverly', 'MA', '01915', 2000, date())
BEGIN TRANSACTION trans1;
+
  INSERT INTO accounts (ORD_VALUE) VALUES (30)
INSERT INTO customer
+
  BEGIN TRANSACTION trans2
(TITLE, LAST_NAME, FIRST_NAME, INITIAL, STREET,
+
  INSERT INTO accounts (ORD_VALUE) VALUES (60)
CITY, STATE, ZIP,LIMIT, START_DATE)
+
  // Rollback the trans1 transaction and any transactions
VALUES
+
  // nested in trans1
('Ms', 'Jones', 'Susan', 'B', '177 High Street','Beverly', 'MA', '01915', 2000, date());
+
  ROLLBACK TRANSACTION trans1
INSERT INTO accounts (ORD_VALUE) VALUES (30);
+
END TRANSACTION
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;
+
// End of program
+
 
</code>
 
</code>
  
  
 
==Products==
 
==Products==
Recital Database Server, Recital Mirage Server, Recital Terminal Developer
+
Recital Server, Recital  
 
[[Category:Documentation]]
 
[[Category:Documentation]]
 
[[Category:SQL|BEGIN TRANSACTION]]
 
[[Category:SQL|BEGIN TRANSACTION]]
 
[[Category:Commands]]
 
[[Category:Commands]]

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