На мой взгляд триггеры оправдывают себя только в том случае, если доступ к таблицам возможен из различных приложений и при этом необходимо обеспечить логическую целостность БД. Если же БД 'закрытая' и доступ к данным обеспечивается только путем вызова хранимых процедур триггеры особо и не нужны. Кроме того использование триггеров в этом случае может привести к понижению производительности за счет:
а) возможной дополнительной обработки таблиц INSERTED, DELETED;
б) исключения возможности использования SQL Server-ом 'прямых' обновлений записей при UPDATE-ах.

А транзакцию правильней открывать, закрывать и откатывать в самой процедуре.