Save point in database transaction

SavePoint:A user can set a savepoint, or marker, within a transaction. Savepoint is a location to which a transaction can return if part of the transaction is conditionally canceled. If a transaction is rolled back to a savepoint.

So in general we have initiated a transaction, it either all rollback or all committed as per below statement.

BEGIN TRANSACTION   — Use for Starting a Transaction

ROLLBACK                         — Use to rollback Transaction

COMMIT                             — Use to commit transaction

In the above there is no way to rollback with specific point, to rollbacking to a specific point we used savepoints.

Syntax:

SAVE TRAN SAVEPOINT_NAME

SAVE TRANSACTION  SAVEPOINT_NAME

Example:

Points to remember:

  1. Duplicate savepoint names are allowed in a transaction.
  2. ROLLBACK TRANSACTION statement that specifies the savepoint name will only roll the transaction back to the most recent SAVE TRANSACTION.
  3. SAVE TRANSACTION is not supported in distributed transactions.