For You SQL Wizards!

We are having a little question and answer contest over the month of November. Question #6 is
Question #6
Your company manages all of its data using Microsoft SQL Server and develops applications and components using Microsoft Visual Basic and Microsoft Office 97. At the end of each day, a client application calls a SQL Server computer to update records in two tables. The updates are based on the values of various fields in a third table. If any modification to either of the two modified tables does not complete, then you want the entire operation to fail so that no records are updated. How should you implement this?

a. Write a single stored procedure that will modify both of the tables. Use a single transaction inside the stored procedure and return an error to the client application if the transaction does not complete.
b. Write a single stored procedure that will modify both of the tables. Use two transactions inside the stored procedure, but only return information to the client application if the transaction completes successfully.
c. Write a separate stored procedure to modify each table. Use a single transaction to nest both stored procedure calls and return an error to the client application only if the transaction does not complete.
d. Write a separate stored procedure to modify each table. Use a separate transaction for each table and return a unique error code for the table that causes the first failure of the update transaction.


Does any one know the correct answer?