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.