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. 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.
B.
. D efine a t emporary t able b efore t he T RY b lock b y u sing t he s ame c olumns a s
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.
C.
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.
D.
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 the target
tables. 5. Commit the transaction if one exists.
Explanation: