datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to ROLLBACK a transaction?

Execute the following SQL Server T-SQL example scripts in SSMS Query Editor to demonstrate the ROLLBACK of a transaction.

When ROLLBACK TRANSACTION is executed UPDATE(s), INSERT(s) and DELETE(s) do not take.

-- SQL rollback transaction - SQL rollback transaction on error

SELECT PurchaseOrderID, ShipMethodID

FROM [AdventureWorks].[Purchasing].[PurchaseOrderHeader]

WHERE PurchaseOrderID = 3412

/*

PurchaseOrderID   ShipMethodID

3412              1

*/

 

BEGIN TRANSACTION

-- SQL update  

UPDATE [AdventureWorks].[Purchasing].[PurchaseOrderHeader]

SET [ShipMethodID] = 4

,[TaxAmt] = 200.0

WHERE PurchaseOrderID = 3412

 

/***** Data only changed tentatively in this session (connection) only *****/

/***** Other connections still see [ShipMethodID]: 1                   *****/

SELECT PurchaseOrderID, ShipMethodID

FROM [AdventureWorks].[Purchasing].[PurchaseOrderHeader]

WHERE PurchaseOrderID = 3412

/*

PurchaseOrderID   ShipMethodID

3412              4

*/

-- COMMIT TRANSACTION would make the change final in the database

ROLLBACK TRANSACTION

 

-- Data in table unchanged after rollback

SELECT PurchaseOrderID, ShipMethodID

FROM [AdventureWorks].[Purchasing].[PurchaseOrderHeader]

WHERE PurchaseOrderID = 3412

/*

PurchaseOrderID   ShipMethodID

3412              1

*/

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

 

-- SQL Server rollback transaction

-- SQL Server rollback transaction on error

USE AdventureWorks;

SELECT ProductID, ProductName=Name, ListPrice

FROM Production.Product

WHERE ProductID = 800

/*

ProductID   ProductName             ListPrice

800         Road-550-W Yellow, 44   1120.49

*/

 

BEGIN TRANSACTION

  UPDATE Production.Product SET ListPrice = ListPrice + 1.0

  WHERE ProductID = 800

    IF @@ERROR != 0

      BEGIN

        ROLLBACK TRANSACTION

      END

    ELSE

    UPDATE Production.Product SET ProductID = 801

    WHERE ProductID = 800

     IF @@ERROR != 0

      BEGIN

-- ROLLBACK occurs here for both UPDATEs

        ROLLBACK TRANSACTION

      END

    ELSE

 COMMIT TRANSACTION

 GO

 /* Error message

 Msg 8102, Level 16, State 1, Line 20

Cannot update identity column 'ProductID'.

*/

 

-- ListPrice update did not take due to ROLLBACK

SELECT ProductID, ProductName=Name, ListPrice

FROM Production.Product

WHERE ProductID = 800

/*

ProductID   ProductName             ListPrice

800         Road-550-W Yellow, 44   1120.49

*/

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


 

Exam Prep 70-461
Exam 70-461