Difference between revisions of "SQL ROLLBACK"

From Recital Documentation Wiki
Jump to: navigation, search
(Products)
Line 28: Line 28:
 
==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
+
  // Rollback the trans1 transaction  
(TITLE, LAST_NAME, FIRST_NAME, INITIAL, STREET,
+
  ROLLBACK TRANSACTION trans1
CITY, STATE, ZIP,LIMIT, START_DATE)
+
  END TRANSACTION
VALUES
+
('Ms', 'Jones', 'Susan', 'B', '177 High Street','Beverly', 'MA', '01915', 2000, date());
+
INSERT INTO accounts (ORD_VALUE) VALUES (30);
+
// Rollback the trans1 transaction  
+
ROLLBACK TRANSACTION trans1;
+
END TRANSACTION;
+
// End of program
+
 
</code>
 
</code>
  

Revision as of 15:29, 22 December 2009

Purpose

Ends the current transaction, undoing all changes


Syntax

ROLLBACK [TRANSACTION <transaction> | <savepoint>] [WORK] [TO SAVEPOINT <savepoint>]


See Also

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


Description

The ROLLBACK statement ends the current or specified transaction and any transactions that are nested within it and discards all changes performed in the transaction or transactions.

TRANSACTION <transaction> | <savepoint> The optional TRANSACTION <transaction> | <savepoint> is used to specify the name of the transaction to be rolled back. The <transaction> is the name of the transactions defined in the BEGIN TRANSACTION <transaction> statement. The <savepoint> is the name defined in the SAVE TRANSACTION <savepoint> statement.

WORK The optional WORK keyword is included for SQL ANSI 92 compatibility. ROLLBACK WORK and ROLLBACK operate in the same way.

TO SAVEPOINT <savepoint> The optional TO SAVEPOINT <savepoint> clause causes a partial rollback of the transaction to be carried out. Changes made since the specified <savepoint> was declared are discarded and the transaction continues from the <savepoint>.

A transaction is a sequence of SQL statements that Recital treats as a single unit. A transaction begins with the first executable SQL statement after a BEGIN TRANSACTION. A transaction ends with a COMMIT, ROLLBACK or END TRANSACTION.


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)
  // Rollback the trans1 transaction 
  ROLLBACK TRANSACTION trans1
  END TRANSACTION


Products

Recital Server, Recital