Difference between revisions of "SQL BEGIN TRANSACTION"
Helengeorge (Talk | contribs) |
Yvonnemilne (Talk | contribs) |
||
| (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"> | ||
| − | + | 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()) | |
| − | 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 | + | |
| − | + | ||
</code> | </code> | ||
==Products== | ==Products== | ||
| − | Recital | + | 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
