Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

SAVE TRANSACTION AND SAVEPOINT

SAVE TRANSACTION AND SAVEPOINT:

Creates points within groups of transactions in which to ROLLBACK.Save points offer a mechanism to roll back portions of transactions. A user can decide and set a savepoint, within a transaction. The savepoint defines a location to which a transaction can return if part of the transaction is conditionally canceled.

Using Save Tran command we can create save point in SQL SERVER ,following example explains Save point in detail.

Syntax:

SAVE { TRAN | TRANSACTION } { savepoint_name |
@savepoint_variable }
[ ; ]

 

Example (SAVE TRANSACTION AND SAVEPOINT):

Original Data before transaction:

T-Sql Query with transaction :


 USE LocalTest
SELECT 'Bfore BEGIN TRAN Parent ', @@TRANCOUNT
   -- The value of @@TRANCOUNT is 0
 
BEGIN TRAN Parent
    SELECT 'After BEGIN TRAN Parent', @@TRANCOUNT
   -- The value of @@TRANCOUNT is 1
    Update Employee
         SET PayScale=PayScale+20 where Country='USA'
    SAVE TRAN PayScaleUS  -- Mark a save point
    SELECT 'After SAVE TRAN PayScaleUS', @@TRANCOUNT
   -- The value of @@TRANCOUNT is still 1
 
    BEGIN TRAN nested
        SELECT 'After BEGIN TRAN nested', @@TRANCOUNT
        -- The value of @@TRANCOUNT is 2
        Update Employee
         SET PayScale=PayScale+50 where Country='India'
        SAVE TRAN PayScaleIND  -- Mark a save point
        SELECT 'After SAVE TRAN PayScaleIND', @@TRANCOUNT
        -- The value of @@TRANCOUNT is still 2
    ROLLBACK TRAN PayScaleUS
 
    SELECT 'After ROLLBACK TRAN PayScaleUS', @@TRANCOUNT
     -- The value of @@TRANCOUNT is still 2
 
    SELECT * FROM Employee
 
IF (@@TRANCOUNT > 0) BEGIN
    ROLLBACK TRAN
    SELECT 'AFTER ROLLBACK TRAN', @@TRANCOUNT
    -- The value of @@TRANCOUNT is 0 because
    -- ROLLBACK TRAN always rolls back all transactions and sets @@TRANCOUNT
    -- to 0.
END


After Executing Transaction:

Invalid entry,please enter valid data.

Loading