Nested transactions are complex and "dangerous". There is a raw limit of 32 on the maximum nested level (on both SQL Server 2000 and 2005) which you can hit if you are not careful. Triggers complicate this things further and therefore the logic on the client code gets really convoluted.

I beleive those reasons should be enough for keeping you from getting into the nested transactions world. I have been there and it is really ugly. Most non trivial applications employ the single transaction model for speed and clarity.

Good Luck,