You are employed as a database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. Your duties include administrating a SQL Server 2005 database server named Certkiller -DB01.
A Domain.com user named Ally Wagner is a member of the Research and Development department. You instruct her to create a stored procedure that will delete data from the Contact table in a Certkiller -DB01. The stored procedure encompasses the subsequent Transact-SQL statement to handle any errors that occur.
BEGIN TRY
BEGIN TRANSACTION
DELETE FROM CK_Sales.Products
WHERE ProductID = @ProductID
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @ErrorMessage nvarchar(2000)
DECLARE @ErrorSeverity int
DECLARE @ErrorState int
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE()
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState) END CATCH
Ally Wagner tests the stored procedure and detects that it leaves open transactions. You inform her to modify the stored procedure in order that it properly handles the open transactions.
What should she do?
A.
Ally Wagner should add a ROLLBACK TRANSACTION command to the CATCH block.
B.
A COMMIT TRANSACTION command should be added to the CATCH block.
C.
In order for the stored procedures to properly handle the open transactions she should add a ROLLBACK TRANSACTION command to the TRY block.
D.
It is important that Ally Wagner remove the COMMIT TRANSACTION command from the TRY block to properly handle the open transactions.
Explanation:
If an exception occurs from one of the statements in the TRY block, control is branched to the CATCH block, where the exception can be handled, logged, and so on. In this scenario you want to ROLLBACK the transaction if there is an error.