Transactions within Triggers

I’ve just spotted quite an interesting question of the day on SQL Server Central from Friday.

It’s to do with how transactions behave inside a trigger.  I selected 4 as my answer as I thought the transaction only existed within the trigger itself and did not affect the calling transaction.  As the trigger is an “after” trigger, I assumed it would write the data in the second insert statement, throw the error and then continue to write the data in the third and fourth insert statements.  It turns out this is not the case and a rollback statement in a trigger will roll back the open transaction (in this case the second insert statement) and then kill the whole batch so no further statements are executed.

Identify currently executing statements

Very handle piece of SQL pinched from

    ,DB_NAME(database_id) AS DatabaseName
    ,SUBSTRING(qt.text,(er.statement_start_offset/2) + 1
            WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
            ELSE er.statement_end_offset
        END - er.statement_start_offset)/2) + 1
    ) AS [Individual Query]
    ,qt.text AS [Parent Query]
    ,es.program_name, er.start_time, qp.query_plan
    ,er.wait_type, er.total_elapsed_time, er.cpu_time, er.logical_reads
    ,er.blocking_session_id, er.open_transaction_count, er.last_wait_type
FROM sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions es ON es.session_id = er.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp
WHERE es.is_user_process = 1 
AND es.session_Id NOT IN (@@SPID)
ORDER BY es.session_id