tailieunhanh - Setting a Savepoint

Setting a Savepoint You can set a savepoint anywhere within a transaction. This allows you to roll back any changes made to database rows after your savepoint. | Setting a Savepoint You can set a savepoint anywhere within a transaction. This allows you to roll back any changes made to database rows after your savepoint. This might be useful if you have a very long transaction because if you make a mistake after you ve set a savepoint you don t have to roll back the transaction all the way to the start. Setting a Savepoint Using T-SQL You set a savepoint in T-SQL using the SAVE TRANSACTION statement or the shorthand version SAVE TRANS. The syntax for this statement is as follows SAVE TRANS ACTION savepointName @savepointVariable where savepointName specifies a string containing the name you want to assign to your savepoint. savepointVariable specifies a T-SQL variable that contains your savepoint name. Your variable must be of the char varchar nchar or nvarchar data type. The following example sets a savepoint named SaveCustomer SAVE TRANSACTION SaveCustomer Let s look at a complete T-SQL example script that sets a savepoint within a transaction. Listing shows a T-SQL script that performs the following steps 1. Begins a transaction. 2. Inserts a row into the Customers table with a CustomerlD of J8COM. 3. Sets a savepoint. 4. Inserts a row into the Orders table with a CustomerID of J8COM. 5. Performs a rollback to the savepoint which undoes the previous insert performed in step 4 but preserves the insert performed in step 2. 6. Commits the transaction which commits the row inserted into the Customers table in step 2. 7. Selects the new row from the Customers table. 8. Attempts to select the from the Orders table that was rolled back in step 5. 9. Deletes the new row from the Customers table. Listing illustrates how to use a savepoint USE Northwind - step 1 begin the transaction BEGIN TRANSACTION - step 2 insert a row into the Customers table INSERT INTO Customers CustomerlD CompanyName VALUES J8COM J8 Company - step 3 set a savepoint .