We will summarize the transaction from the beginning. Let's start with a simple transaction example. The following example first defines a transaction, and then records the data about a new order to the database. The sample code is as follows:
Use tsqlfundamentals2008; go -- transaction -- a simple transaction begin Tran; -- declare a variable and save the new orderiddeclare @ neworderid as Int = 0; -- Insert a new order to sales. insert into sales in the orders table. orders (custid, empid, orderdate, requireddate, shippeddate, shipperid, freight, shipname, shipaddress, shipcity, shipregion, shippostalcode, shipcountry) values (85, -- custid-int 5, -- empid-int '20170101', -- orderdate-datetime '20160301', -- requireddate-datetime '20160301', -- shippeddate-datetime 3, -- shipperid-int 20090212, -- freight-money N 'ship to 85-b', -- shipname-nvarchar (40) n '1996 Rue de l', -- shipaddress-nvarchar (60) n 'abbaye ', -- shipcity-nvarchar (15) N 'reims ', -- shipregion-nvarchar (15) N '000000', -- shippostalcode-nvarchar (10) N 'France '-- shipcountry-nvarchar (15); -- save the New Order ID to the variable set @ neworderid = scope_identity (); -- or @ identity -- return the New Order idselect @ neworderid as neworderid; -- Insert the Order details of the new order to sales. insert into sales in the orderdetails table. 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); -- submit the transaction commit Tran;
The effect of data insertion is as follows:
We can see that one order and three order details are successfully inserted.
After the test is complete, run the following code to clean up the data.
-- Clear data Delete from sales. orderdetails where orderid> 11077; delete from sales. orders where orderid> 11077; DBCC checkident ('sales. orders ', reseed, 11077); -- reset the id value
A simple transaction