Difference between revisions of "SQL ROLLBACK"

Latest revision as of 11:06, 11 December 2012

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 Lianja 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

Lianja Server, Lianja