Извините за длинный пост, так получилось :(
Итак,
фрагмент кода триггера на insert таблицы TABLE2 (в угловых скобках номер строки):
...
<12>/*
<13> * PREVENT NULL VALUES IN 'FIELD1'
<14> */
<15>IF (SELECT Count(*) FROM inserted WHERE FIELD1 IS NULL) > 0
<16> BEGIN
<17> RAISERROR 44444 'Field ''FIELD1'' cannot contain a null value.'
<18> ROLLBACK TRANSACTION
<19> END
<20>/*
<21> * PREVENT INSERTS IF NO MATCHING KEY IN 'TABLE1'
<22> */
<23>IF (SELECT COUNT(*) FROM inserted) !=
<24> (SELECT COUNT(*) FROM TABLE1, inserted WHERE (TABLE1.FIELD2 = inserted.FIELD2))
<25> BEGIN
<26> RAISERROR(779025, 16, 1)
<27> ROLLBACK TRANSACTION
<28> END
...

вот фрагмент кода процедуры:
...
BEGIN
BEGIN TRANSACTION
INSERT INTO DBO.TABLE1 (FIELD1)
VALUES (123)
INSERT INTO DBO.TABLE2 (FIELD1, FIELD2) VALUES (123, @PARAM1)
COMMIT TRANSACTION
SELECT @RETURN_CODE = FIELD2 FROM DBO.TABLE1 WHERE FIELD1 = 123
IF @RETURN_CODE IS NULL
-- ошибка вставки
SET @RETURN_CODE = -6
END
...

вот код, вызываемый в QA:
DECLARE @C INT
EXEC .SP_MY_PROCEDURE @C OUTPUT, NULL -- @PARAM1 = NULL, т.е. попытка вставить NULL в not nullable-поле
SELECT @C
GO

вот что возвращается после выполнения кода:
Server: Msg 3903, Level 16, State 1, Procedure TABLE2_ITrig, Line 27
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
Server: Msg 44444, Level 16, State 1, Procedure TABLE2_ITrig, Line 17
Field 'FIELD1' cannot contain a null value.
Server: Msg 779025, Level 16, State 1, Procedure TABLE2_ITrig, Line 26
Cannot add or change record. Referential integrity rules require a related record in table ''TABLE1''.
The statement has been terminated.

т.е. происходит следующее:
в процедуре выполняется 2-й insert в транзакции (1-й проходит успешно, как и задумано), срабатывает триггер,
проверка (строки 12-19) не проходит, рейзится ошибка (строка 17), происходит роллбэк до начала транзакции в процедуре.
Триггер продолжает выполняться, рейзится 2-я ошибка (строка 26), т.к. 1-й роллбэк откатил данные 1-го инсерта,
затем попытка выполнить роллбэк уже несуществующей транзакции (строка 27).
Таким образом, все стройно и логично :)
Но...
Во-первых, смущает порядок рейзания ошибок - сначала на 27-й, потом на 17-й и в конце на 26-й строке.
Во-вторых, почему 2 первых ошибки не приводят к завершению триггера, а только 3-я (уровень грубости у всех одинаковый)?
В-третьих, триггер завершает работу процедуры, чего хотелось бы избежать...