nerdexam
Microsoft

70-451 · Question #64

You are a database developer. You plan to design a database solution by using SQL Server 2008. A database contains a stored procedure that is created by using the following DDL code segment. You…

The correct answer is D. - Add a SAVE TRANSACTION TR1 statement after the INSERT statement. See the full explanation below for the reasoning.

Question

You are a database developer. You plan to design a database solution by using SQL Server 2008. A database contains a stored procedure that is created by using the following DDL code segment. You discover that the UPDATE statement occasionally throws an exception that causes the entire transaction to roll back. You need to ensure that when the UPDATE statement causes an exception, SP1 performs the following tasks:

  • The INSERT statement is committed,
  • All data is in a consistent state.
  • The Transaction count is equal to 0 after execution completes.

What should you do?

Options

  • A
    • Add a TRY/CATCH block after the INSERT statement.
  • B
    • Add a TRY/CATCH block after the INSERT statement.
  • C
    • Add an XACT_ABORT ON statement to the first line of the procedure.
  • D
    • Add a SAVE TRANSACTION TR1 statement after the INSERT statement.

How the community answered

(46 responses)
  • A
    9% (4)
  • B
    20% (9)
  • C
    2% (1)
  • D
    70% (32)

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-451 Practice