一个简单的事务

USE TSQLFundamentals2008;
GO

-- 事务
-- 一个简单的事务
BEGIN TRAN;

-- 声明一个变量，保存新的OrderID
DECLARE @neworderid AS INT=0;

-- 将一个新订单插入到Sales.Orders表中
INSERT INTO Sales.Orders
        ( custid ,
          empid ,
          orderdate ,
          requireddate ,
          shippeddate ,
          shipperid ,
          freight ,
          shipname ,
          shipaddress ,
          shipcity ,
          shipregion ,
          shippostalcode ,
          shipcountry
        )
VALUES  ( 85 , -- custid - int
          5 , -- empid - int
          ‘20090212‘ , -- orderdate - datetime
          ‘20090301‘ , -- requireddate - datetime
          ‘20090216‘ , -- shippeddate - datetime
          3 , -- shipperid - int
          32.38 , -- freight - money
          N‘ship to 85-b‘ , -- shipname - nvarchar(40)
          N‘6789 rue de l‘ , -- shipaddress - nvarchar(60)
          N‘Abbaye‘ , -- shipcity - nvarchar(15)
          N‘Reims‘ , -- shipregion - nvarchar(15)
          N‘10345‘ , -- shippostalcode - nvarchar(10)
          N‘France‘  -- shipcountry - nvarchar(15)
        );
        
-- 将新的订单ID保存到变量中
SET @neworderid= SCOPE_IDENTITY(); --或者@@IDENTITY

-- 返回新的订单ID
SELECT @neworderid AS neworderid;

-- 将新订单的订单明细插入到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 TRAN;

-- 清理数据
DELETE FROM Sales.OrderDetails WHERE orderid>11077;
DELETE FROM Sales.Orders WHERE orderid>11077;
DBCC CHECKIDENT(‘Sales.Orders‘,RESEED,11077); -- 重置标识值