In regards to the discussion of pseduo coding this question and that fact that it might be a red hearing to give the information on no parent child relationship.

I belive that this is a great question, to those of us that have taken microsoft exams know, they are filed with questions worded such as these, they test you not only on your knowledge of the problem, but also on your ability to take the information given, discount what is not revelent to the issue and make your decision based on the facts given. Yes its a little hard to read and takes a minute to understand, but when faced with a issue in real life, no one is going to pseduo code the problem for you and take out what is not relevent.

The only answer of course is the correct answer given, complete rollback.

Also, Triggers are always run inside of a transaction by default, you do not have to explicity identifiy one.

There is no transaction, according to the question. You are coming into the trigger without being in the context of a transaction. In other words, there was no "begin transaction" executed anywhere before the trigger started.

There is ALWAYS a transaction whenever any kind of data modification occurs. Each DML statement starts an implicit transaction. You can choose to enlist one or more DML statements in an explicit transaction (using BEGIN TRAN / ROLLBACK / COMMIT), but there is no way to turn off the implicit transaction. That's one of the many features in SQL Server that helps to ensure the ACID properties.

No, that's incorrect. Implicit transactions create an implicit multistatement transaction. But single statements still have their own implicit transactions. Take this batch, for instance:

INSERT Tbl VALUES ('w', 'x')INSERT Tbl VALUES ('y', 'z')

Each of these inserts will start and end its own transaction (implicit -- because you are not explicitly starting a tran). EVERY DATA MODIFICATION USES A TRANSACTION. You cannot turn that off.

If you want to enlist both of these inserts in a single transaction (so that, e.g., if one fails the other can be rolled back with it), you have two choices:

BEGIN TRAN -- do work(COMMIT or ROLLBACK)

or:

SET IMPLICIT_TRANSACTIONS ON -- do work(COMMIT or ROLLBACK)

The difference between these is that if you use IMPLICIT_TRANSACTIONS, another multistatement tran starts automatically as soon as any data modification occurs. With BEGIN TRAN, you're controlling it, and you can let statements run using their own, atomic transactions.

Understanding how transactions work is extremely important when working with any DBMS. I highly recommend picking up Kalen Delaney's book, _Inside SQL Server 2000_, if any of this is new to you.