This surprised me. I have a BizTalk SQL adapter calling a stored procedure to insert a row in some table. The table has an "AFTER INSERT" trigger on it.
The original design had the trigger fire off an SSIS package. But when the SSIS is called, the row status is still not committed. I thought since I'm using an "AFTER INSERT" trigger, I was allowed to do that. Need to read more on the transaction nature of triggers, I guess.