Difference between revisions of "SQL ROLLBACK"

Revision as of 16:21, 25 November 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

// config.db
set sql to recital
set sql on
// end of config.db
 
// Transactions
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;
// End of program

Products

Recital Server, Recital