Note: This question is part of a series of questions that present the same scenario. Each question in
the series contains a unique solution that might meet the stated goals. Some question sets might have
more than one correct solution, while others might not have a correct solution.
After you answer a question in this section. You will NOT be able to return to it. As a result, these
questions will not appear in the review screen.
You create a table named Products by running the following Transact-SQL statement:
You have the following stored procedure:
You need to modify the stored procedure to meet the following new requirements:
Insert product records as a single unit of work.
Return error number 51000 when a product fails to insert into the database.
If a product record insert operation fails, the product information must not be permanently written to the
database.
Solution: You run the following Transact-SQL statement:
Does the solution meet the goal?
A.
Yes
B.
No
Explanation:
With X_ABORT ON the INSERT INTO statement and the transaction will be rolled back when an error is
raised, it would then not be possible to ROLLBACK it again in the IF XACT_STATE() <> O ROLLACK
TRANSACTION statement.
Note: A transaction is correctly defined for the INSERT INTO ..VALUES statement, and if there is an error in
the transaction it will be caughtant he transaction will be rolled back, finally an error 51000 will be raised.
Note: When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire
transaction is terminated and rolled back.
XACT_STATE is a scalar function thatreports the user transaction state of a current running request.
XACT_STATE indicates whether the request has an active user transaction, and whether the transaction is
capable of being committed.
The states of XACT_STATE are:
0 There is no active user transaction for the current request.
1 The current request has an active user transaction. The request can perform any actions, including writing
data and committing the transaction.
2 The current request has an active user transaction, but an error hasoccurred that has caused the
transaction to be classified as an uncommittable transaction.https://msdn.microsoft.com/en-us/library/ms188792.aspx
https://msdn.microsoft.com/en-us/library/ms189797.aspx
Why?
If a product record insert operation fails, the product information must not be permanently written to the database. Meaning for both XACT_STATE() =1 and XACT_STATE() = -1
Hence If XACT_STATE() 0.
What do you think?
Answer B is correct.
Using XACT_ABORT ON will work as described in the explanation. XACT_STATE () will return 0 as the rollback is already performed.
See sample code below from Microsoft documentation slightly enhanced:
IF OBJECT_ID(N’t2′, N’U’) IS NOT NULL
DROP TABLE t2;
GO
IF OBJECT_ID(N’t1′, N’U’) IS NOT NULL
DROP TABLE t1;
GO
CREATE TABLE t1
(a INT NOT NULL PRIMARY KEY);
CREATE TABLE t2
(a INT NOT NULL REFERENCES t1(a));
GO
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (3);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (6);
GO
SET XACT_ABORT OFF;
GO
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2); — Foreign key error.
INSERT INTO t2 VALUES (3);
SELECT 100 + XACT_STATE();
COMMIT TRANSACTION;
GO
SET XACT_ABORT ON;
GO
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (4);
INSERT INTO t2 VALUES (5); — Foreign key error.
INSERT INTO t2 VALUES (6);
SELECT 200 + XACT_STATE();
COMMIT TRANSACTION;
GO
SELECT 300 + XACT_STATE();
— SELECT shows only keys 1 and 3 added.
— Key 2 insert failed and was rolled back, but
— XACT_ABORT was OFF and rest of transaction
— succeeded.
— Key 5 insert error with XACT_ABORT ON caused
— all of the second transaction to roll back.
SELECT *
FROM t2;
GO
The question wants us : ” Return error number 51000 ” not Raising Error.
Code is correct but because stored procedure dose not return number the Answer is B.
Watch Pluralsight-SQL Server 2012: Transact-SQL Error Handling . it is helpful.
My explanation is wrong.
I run the above question on SQL Server and Result is :
“Incorrect syntax near ‘50001’.”
The code has a textual problem.
Because after “Rollback Transaction” there is not semicolon , It works like this :
ROLLBACK TRANSACTION savepoint_name.
However, the option B is correct but explanation of answer is wrong.