В чем вопрос: в хранимой процедуре есть два INSERT, следующие друг за другом. Они заключены в рамки транзакции.
Первый INSERT записывает данные в таблицу с триггером. Триггер работает на INSERT/UPDATE.
Когда данные неверные вносятся - все хорошо, триггер ругается, НО отката транзакции нет и сразу за
первым INSERT-ом отрабатывает второй.
В теле триггера есть выражение SET XACT_ABORT ON, но и это не помогает.
Как прервать выполнение процедуры и поднять описание ошибки из триггера?

Все, сделал)))
Поставил выполнение INSERT-ов в блок TRY-CATCH и в CATCH - делаю RAISERROR  

Можно еще анализировать переменную @@error.