创建一个存储过程,该过程里使用了其它两个存储过程,其中每个存储过程都要在插入数据之前,都要申请一个主PK,
如果在外面加上
begin tran t1
exec A ---存储过程A申请主PK
exec B begin try ---给个陷阱
select 1/0
end try
BEGIN CATCH
ROLLBACK tran t1 --回滚事务
return ---返回
end cath commit tran t1上面中希望存储过程A完成主PK的申请,但不要回滚.但其它插入的数据可能被回滚过去,因为一但回滚了,其它端口发生主PK申请时,可能会发生PK冲突.
不知该怎么做?
如果在外面加上
begin tran t1
exec A ---存储过程A申请主PK
exec B begin try ---给个陷阱
select 1/0
end try
BEGIN CATCH
ROLLBACK tran t1 --回滚事务
return ---返回
end cath commit tran t1上面中希望存储过程A完成主PK的申请,但不要回滚.但其它插入的数据可能被回滚过去,因为一但回滚了,其它端口发生主PK申请时,可能会发生PK冲突.
不知该怎么做?
go
create table t (ID int)
--drop view testcreate view vFaulted
as
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
gobegin try
begin tran
insert T values(1)
insert T values('a')
commit tran
end try
begin catch
select * from test
rollback tran
end catch
gobegin tran
begin try
insert T values(1)
insert T values('a')
end try
begin catch
select * from test
if @@trancount>0
rollback tran
end catch
if @@trancount>0
commit tran
goset xact_abort on ;
begin try
begin tran
insert T values(1)
insert T values('a')
commit tran
end try
begin catch
select * from test
if (xact_state())=-1
rollback tran
if (xact_state())=1
commit tran
end catch
go
select * from T