Savepoint command in SQL Server 2008
In this article I am going to explain how to use Savepoint command in SQL Server 2008.
Introduction
Savepoint command in SQL Server is used to rollback the transaction into a certain point. Sometimes a situation arises In which a user want to modify a table into a database. A user wants to delete some records into the database. After deleting a record into a database the user want to reached the old point in the transaction we can used the save point.
The syntax for SAVEPOINT command is as follows
SAVEP Transaction SAVEPOINT NAME; |
The syntax for rolling back to a SAVEPOINT is as follows
ROLLBACK Transaction SAVEPOINT_NAME;
|
Consider table(mcnnew ) is having following records:
create table mcnnew
(
id int not null,
Name varchar(15),
Age int,
salary varchar(25),
) |
The records showing in following figure
Delete the record from the mcnnew
Delete the record from mcnnew where ID = 3
and ID = 4;
Save the Points
begin tran del
save tran p1
delete from mcnnew where id =3
|
Show the result
begin tran de
save tran p2
delete from mcnrep where id = 4
|
Show the result
Roll Back to the certain point
Rollback transaction to the P2
Show the result
Rollback transaction to the P1
Show the result