Sql server® 2012 t-sql fundamentals

 

CHAPTER 9

 

Transactions and Concurrency

 

 299

(replaying) all of the changes from any transaction whose commit instruction is written to 

the log but whose changes haven’t yet made it to the data portion. The undo phase involves 

rolling back (undoing) the changes from any transaction whose commit instruction was not 

recorded in the log.

For example, the following code defines a transaction that records information about a new order 

in the TSQL2012 database.

USE TSQL2012; 

 

-- Start a new transaction 

BEGIN TRAN; 

 

  -- Declare a variable 

  DECLARE @neworderid AS INT; 

 

  -- Insert a new order into the Sales.Orders table 

  INSERT INTO Sales.Orders 

      (custid, empid, orderdate, requireddate, shippeddate,  

       shipperid, freight, shipname, shipaddress, shipcity, 

       shippostalcode, shipcountry) 

    VALUES 

      (85, 5, '20090212', '20090301', '20090216', 

       3, 32.38, N'Ship to 85-B', N'6789 rue de l''Abbaye', N'Reims', 

       N'10345', N'France'); 

 

  -- Save the new order ID in a variable 

  SET @neworderid = SCOPE_IDENTITY(); 

 

  -- Return the new order ID 

  SELECT @neworderid AS neworderid; 

 

  -- Insert order lines for the new order into Sales.OrderDetails 

  INSERT INTO Sales.OrderDetails 

      (orderid, productid, unitprice, qty, discount) 

    VALUES(@neworderid, 11, 14.00, 12, 0.000), 

          (@neworderid, 42, 9.80, 10, 0.000), 

          (@neworderid, 72, 34.80, 5, 0.000); 

 

-- Commit the transaction 

COMMIT TRAN;

The transaction’s code inserts a row with the order header information into the Sales.Orders table 

and a few rows with the order lines information into the Sales.OrderDetails table. The new order ID is 

produced automatically by SQL Server because the orderid column has an identity property. Immedi-

ately after the code inserts the new row into the Sales.Orders table, it stores the newly generated order 

ID in a local variable, and then it uses that local variable when inserting rows into the Sales.OrderDetails 

table. For test purposes, I added a SELECT statement that returns the order ID of the newly generated 

order. Here’s the output from the SELECT statement after the code runs.

neworderid 

----------- 

11078

www.it-ebooks.info

---

300  

Microsoft SQL Server 2012 T-SQL Fundamentals

Note that this example has no error handling and does not make any provision for a ROLLBACK in 

case of an error. To handle errors, you can enclose a transaction in a TRY/CATCH construct. You can 

find an overview of error handling in Chapter 10. 

When you’re done, run the following code for cleanup.

DELETE FROM Sales.OrderDetails 

WHERE orderid > 11077; 

 

DELETE FROM Sales.Orders 

WHERE orderid > 11077;