Good article. I have a small correction. If the after trigger fails (or raises an exception), it does not fail the transaction. The client must handle it.

A well behaved client has code like this:

start transaction

try

update/insert/delete/sp

commit

except

rollback;

other actions like inform the user

end;

More often clients rely on autocommit and since the error condition happens after action, it's committed anyway. You can check this in management studio: put a raiserror in a trigger, execute update without BEGIN TRANSACTION, you have nothing to rollback and updates are in place.