I need the insert of table a and c to be successful even though insert to be fails.

code sample

drop table a

go

create table a

(a1 integer identity, a2 integer)

go

drop table b

go

create table b

(b1 integer identity, b2 integer)

go

drop table c

go

create table c

(c1 integer identity, c2 integer)

go

CREATE TRIGGER [TRaInsert]

ON [a]

FOR INSERT

AS

BEGIN TRY

begin transaction c

begin try

begin transaction b

insert into b (b2) values(1)

select 1/0 -- error created

commit transaction b

end try

begin catch

print 'catch of transaction b'

rollback transaction b

end catch

insert into c (c2) values(1)

commit transaction c

end try

begin catch

select ERROR_MESSAGE()

end catch

go