You are a database developer. You plan to design a database solution by using SQL Server 2008. You create a stored procedure that uses the TRY/CATCH syntax in a new database. When the stored procedure is executed, it logs information about each step in the TRY block into a table named dbo.ExecutionLog. When an error occurs, the stored procedure must perform the following tasks:
* Roll back the changes made to the target tables.
* Retain the log entries stored in the dbo.ExecutionLog table.
You need to ensure that the stored procedure performs the given tasks. What should you do?
A.
1.Start a transaction in the TRY block.
2. After each step, insert log entries into the dbo.ExecutionLog table.
3. In the CATCH block, commit the transaction.
4. After the CATCH block, use data in the dbo.ExecutionLog table to reverse any changes made to thetarget tables.
5. Commit the transaction if one exists.
B.
1. Start a transaction in the TRY block.
2. Before each step, define a transactional save point.
3. After each step, insert log entries into the dbo.ExecutionLog table.
4. In the CATCH block, roll back to the transactional save points.
5. After the CATCH block, commit the transaction.
C.
1. Define a temporary table before the TRY block by using the same columns as that of the dbo.ExecutionLog table.
2. Start a transaction in the TRY block.
3. After each step, insert log entries into the temporary table.
4. In the CATCH block, roll back the transaction.
5. After the CATCH block, insert the rows from the temporary table into the dbo.ExecutionLog table.
6. Commit the transaction if one exists.
D.
1. Define a table variable before the TRY block by using the same columns as that of the dbo.ExecutionLog table.
2. Start a transaction in the TRY block.
3. After each step, insert log entries into the table variable.
4. In the CATCH block, roll back the transaction.
5. After the CATCH block, insert the rows from the table variable into the dbo.ExecutionLog table.
6. Commit the transaction if one exists.