You are a database developer on an instance of SQL Server 2008. You are creating an application that will be used by the inventory department. You write the following Transact-SQL code:
BEGIN TRANSACTION
INSERT INTO InvLookup VALUES (1, ‘Finished Goods’);
BEGIN TRANSACTION
INSERT INTO InvLookup VALUES (7, ‘Component’);
COMMIT TRANSACTION;
UPDATE InvTemp SET ProcFlg = ‘X’WHERE InvID IN (SELECT InvID FROM InvMaster);
UPDATE InvMaster SET ProcFlg = ‘Y’;
ROLLBACK TRANSACTION;
Assuming all DML statements in both the inner and outer transaction process successfully, which statements will be committed?
A. only the statements in the inner transaction
B. only the statements in the outer transaction
C. none of the statements in either the inner or outer transaction
D. all of the statements in both the inner and outer transactions
Correct Answer: C
Explanation/Reference:
When using a nested transaction, the outer transaction controls which modifications are committed or rolled back. With the given Transact-SQL, you issue an INSERT statement within the outer transaction. Then, you begin another transaction using the BEGIN TRANSACTION statement and issue an INSERT statement and a COMMIT TRANSACTION statement within this inner transaction. With a non-nested transaction, the COMMIT TRANSACTION statement would commit the changes. However, with nested transactions, any COMMIT TRANSACTION statement is ignored, and the outer transaction controls which modifications are committed or rolled back. In this scenario, you issue a ROLLBACK TRANSACTION statement at the end of the outer transaction that rolls back all changes made by both transactions. If you had issued a COMMIT TRANSACTION statement for the outer transaction, then all of the DML statements in both transactions would have been committed.
All of the other options are incorrect because when you use nested transactions, the outer transaction controls which modifications are committed or rolled back.