i am repalcing the roll back with raise error. Using follwing code. And it gives me error as shown below the code.

I want the code not to return error but rollback the transaction. Can anybody help to correct the code.

drop table a

go

create table a( i int)

go

create trigger a_insert

on a

for insert

as

begin

declare @a as varchar(40)

set @a = 'count of transaction' + CONVERT(varchar(10), @@TRANCOUNT)

print @a

--rollback

raiserror ( 'aa',16,0)

end

go

DROP PROCEDURE a1

go

create procedure a1 as

begin try

begin tran

insert into a (i) values (2)

select * from a

commit tran

end try

begin catch

if @@TRANCOUNT > 1

rollback tran

end catch

execute a1

It still gives me error message as

count of transaction1

(0 row(s) affected)

Msg 266, Level 16, State 2, Procedure a1, Line 0

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

Msg 3998, Level 16, State 1, Line 1

Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.