To use Savepoint in SQL

[Elkanah]

hello friends,

I want to ask about the keyword savepoint in SQL.
How can I use this keyword ?

any ideas........please suggest

[Praetor]

SQL Statements for creating and controlling savepoints

The following SQL statements enable you to create and control savepoints:

Create Savepoint
To set a savepoint, issue a SAVEPOINT SQL statement. To identify a specific savepoint for nested savepoints and to improve the clarity of your code, you can choose a meaningful name for the savepoint. These names are known as savepoint references.
For example:
SAVEPOINT before_sales ON ROLLBACK RETAIN CURSORS

Release Savepoint
To release a savepoint, issue a RELEASE SAVEPOINT SQL statement.
For example:
RELEASE SAVEPOINT before_sales
If you do not explicitly release a savepoint with a RELEASE SAVEPOINT SQL 7 statement, it is released at the end of the current savepoint level.

Rollback To Savepoint
To rollback to a savepoint, issue a ROLLBACK TO SAVEPOINT SQL statement.
For example:
ROLLBACK TO SAVEPOINT before_sales

[Reegan]

Definition of Savepoint -

A savepoint is a special mark inside a transaction that allows all commands that are executed after it was established to be rolled back, restoring the transaction state to what it was at the time of the savepoint.
SAVEPOINT establishes a new savepoint within the current transaction.

Syntax -

SAVEPOINT savepoint_name

where savepoint_name is a parameter - the name to give to the new savepoint.

Compatibility -

SQL requires a savepoint to be destroyed automatically when another savepoint with the same name is established. In PostgreSQL, the old savepoint is kept, though only the more recent one will be used when rolling back or releasing. (Releasing the newer savepoint will cause the older one to again become accessible to ROLLBACK TO SAVEPOINT and RELEASE SAVEPOINT.) Otherwise, SAVEPOINT is fully SQL conforming.

[kelfro]

Check some of the examples

> To establish a savepoint and later undo the effects of all commands executed after it was established:

BEGIN;
INSERT INTO table1 VALUES (1);
SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (2);
ROLLBACK TO SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (3);
COMMIT;

The above transaction will insert the values 1 and 3, but not 2.

---------------------------------------------------------------------

> To establish and later destroy a savepoint:

BEGIN;
INSERT INTO table1 VALUES (3);
SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (4);
RELEASE SAVEPOINT my_savepoint;
COMMIT;

The above transaction will insert both 3 and 4.

[opaper]

SavePoint Level
A savepoint level refers to the scope of reference for any savepoint-related statement. When a savepoint level is started, no savepoint-related statement can refer to a savepoint created outside the new savepoint level. Similarly, savepoint references are resolved within the current savepoint level and do not take into account savepoint references outside of the current savepoint level.

A new savepoint level is started or entered only when any the following happens:

• A new unit of work is started
• A stored procedure defined with the NEW SAVEPOINT LEVEL clause
• An atomic compound SQL statement is started

A savepoint level is ended when the event that caused its creation is finished or removed.
Following rules apply to actions within a savepoint level's scope:

-- Savepoints can only be referenced within the savepoint level in which they are established.
You cannot release or rollback to a savepoint established outside of the current savepoint level.

-- All active savepoints established within the current savepoint level are automatically released when the savepoint level ends.

-- Savepoint unique names are only enforced within the current savepoint level. The names of savepoints that are active in surrounding savepoint levels can be reused in the current savepoint level without affecting these other savepoints.