The error message is happening because when you do an insert there is an implicit transaction started. This is required for atomicity. Your trigger has now rolled back the transaction that was started from the insert. When control returns to the insert it determines the transaction was rolled back.
The easiest way to avoid this is to NOT handle your transactions inside your trigger.
I have a feeling that this thread is because you are about to embark on nested transactions. Nested transactions in sql are a myth. The engine will let you do what appears to be nested transactions but the reality is they are not actually nested.
This is easy enough to prove.
begin transaction
select @@TRANCOUNT --this will be 1 as we would expect
begin transaction
select @@TRANCOUNT --this will now be 2 as we would expect
rollback transaction
select @@TRANCOUNT --Will this be 1 or 0?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/