You work in an International company named TAKEEEN. And you’re in charge of the database of your company. You intend to use SQL Server 2008 to create a database solution. A stored procedure that uses the TRY/CATCH syntax is created in a new database. When the stored procedure is executed, the stored procedure logs information about each step in the TRY block into a table when it is executed, the table is named dbo.RunningLog. If an error happens, the stored procedure must roll back the changes made to the target tables and retain the log entries stored in the dbo.RunningLog table. So what should you do to make sure that the given tasks can be performed by the stored procedure?
A. In the TRY block, you should start a transaction. Define a transactional save point before each step. Insert log entries into the dbo.RunningLog table after each step. Roll back to the transactional save points in the CATCH block. Commit the transaction after the CATCH block.
B. In the TRY block, you should start a transaction in the TRY block. Insert log entries into the dbo.RunningLog table after each step. Commit the transaction in the CATCH block after the CATCH block. Use data in the dbo.RunningLog table to reverse any changes made to the target tables. Commit the transaction if one exists
C. Before the TRY block, you define a table variable by using the same columns as that of the dbo.RunningLog table. In the TRY block, start a transaction. After each step, insert log entries into the table variable. Roll back the transaction in the CATCH block. After the CATCH block, insert the rows from the table variable into the dbo.RunningLog table. Commit the transaction if one exists
D. Before the TRY block, you should use the same columns as that of the dbo.RunningLog table to define a temporary table by. In the TRY block, start a transaction. After each step, insert log entries into the temporary table. In the CATCH block, roll back the transaction. Insert the rows from the temporary table into the dbo.RunningLog table after the CATCH block.
Commit the transaction if one exists.
