Thursday, June 7, 2012

Transaction In SQL

We use Transaction in sql to avoid accidental data loss.
The keywords used are Begin Tran, Commit Tran, Rollback Tran.

Begin Tran - It is written in the beginning of the sql statements.
Commit Tran - Its is written after the sql statements.
Rollback Tran - It is executed if the user want to revert the changes.

EG:


BEGIN TRAN
UPDATE UiBaseTable
    SET
        Text          = 'Debrief Decision Saved successfully',
        TextKeyDescription    = 'Debrief Decision Saved successfully'
    WHERE
        TEXTKEY      = 'DebriefDecisionSaved'
       
GO

UPDATE UiBaseTable
    SET
        Text             = 'Country default saved successfully',
        TextKeyDescription    = 'Country default saved successfully'
    WHERE
        TEXTKEY   = 'SaveCountryDefault'   
           
GO

UPDATE UiBaseTable
    SET
        Text          = 'Vehicle Unreserved successfully',
        TextKeyDescription    = 'Vehicle Unreserved successfully'
    WHERE
        TEXTKEY       = 'VehicleUnReserved'

COMMIT TRAN

ROLLBACK TRAN

Here we are updating more than one statements. suppose i want to update all or nothing, so if any statement fails i can roll back the changes by executing  ROLLBACK TRAN.

2 comments:

  1. a big dis advantage of this there is no way to log the error with this approach.

    ReplyDelete
  2. Do we have any other methods to avoid data loss like transaction??

    ReplyDelete