What happens when you perform a rollback inside a trigger?

A coworker who is studying for the MCTS exam for 2005 brought this question up and wanted help understanding why the answers in the study materials were correct. After a few minutes of discussion, I decided this was one of those topics that was best demonstrated completely rather than attempting to actually explain it, so I set about creating some demonstrations to help him see what actually would happen. Without posting copyright materials directly, basically the answers choices were:

Data changes made in the current transaction including those made in the trigger are rolled back.

Data changes made in the current transaction except those made in the trigger are rolled back.

Remaining statements in the batch after the one that fired the trigger are still executed.

Remaining statements in the trigger after the rollback statement are still executed.

Nested triggers located after the rollback statement will execute as normal.

Now the correct answers in the above are 1 and 4, but try to explain why to someone who has little experience with triggers, and you will begin to understand the situation. So figuring it was easier to just demonstrate this, I went to creating some demo code:

Msg 3609, Level 16, State 1, Line 3 The transaction ended in the trigger. The batch has been aborted.

This essentially demonstrates the two correct answers from above, the SELECT COUNT(*) and PRINT statements after the rollback in the trigger continued to execute, but the modifications made before and inside of the trigger have been rolled back, which prevented the nested trigger from inserting the row into the triggertestarchive table.

Now for demonstration purposes earlier today, I ran the above code twice, once with the ROLLBACK commented out so that the execution completed normally, and then after dropping the tables, I reran it with the ROLLBACK in place to demonstrate how the ROLLBACK affects the changes being made and the firing of the nested trigger.

Comments

Is it possible for a Rollback in a trigger to only roll back the inner most transaction? A rollback in a trigger will roll back all the way to the outer most tran. I realize there are tran names and save points but I am looking for something more smarter.