Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 28-03-2009
Member
 
Join Date: Feb 2009
Posts: 38
To use Savepoint in SQL

hello friends,

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

any ideas........please suggest
Reply With Quote
  #2  
Old 28-03-2009
Member
 
Join Date: Apr 2008
Posts: 1,948
Re: To use Savepoint in SQL

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
Reply With Quote
  #3  
Old 28-03-2009
Member
 
Join Date: Oct 2005
Posts: 2,389
Re: To use Savepoint in SQL

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.
Reply With Quote
  #4  
Old 28-03-2009
Member
 
Join Date: Apr 2008
Posts: 2,001
Re: To use Savepoint in SQL

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.
Reply With Quote
  #5  
Old 28-03-2009
Member
 
Join Date: May 2008
Posts: 2,383
Re: To use Savepoint in SQL

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.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "To use Savepoint in SQL"
Thread Thread Starter Forum Replies Last Post
Creating savepoint in database afidelino Software Development 4 13-03-2010 10:14 PM


All times are GMT +5.5. The time now is 10:39 PM.