事务中嵌套执行存储过程的问题,一直没搞明白
create table XT_A(f_a datetime)
create table XT_B(f_b datetime)
-----------
CREATE PROCEDURE [XS_TEST] AS
DECLARE @ERR INT
SET @ERR = 0
BEGIN TRAN T1
UPDATE XT_b SET F_b = getdate()
if @@error = 0 -------此行为模拟事务回滚，改成了正确时回滚
begin
SET @ERR = -1
rollback
end
COMMIT TRAN T1
RETURN @ERR
GO
------------执行下列代码
declare @today datetime,@err int
set @today = getdate()
begin tran t1
insert xt_a values(@today)
if @@error<> 0
begin
rollback
goto finish
end
begin tran t2
exec @err = XS_TEST
if @err <> 0
begin
rollback
goto finish
end
commit tran t1
commit tran t2
finish:
select @err
select * from xt_a
---------------------------
执行以上代码出错：COMMIT TRANSACTION 请求没有对应的 BEGIN TRANSACTION。EXECUTE 后的事务计数指出缺少了 COMMIT 或 ROLLBACK TRANSACTION 语句。
我觉得我代码没有写错啊，不知道是不是嵌套的原理根本就没有弄清楚。
还有，我在存储过程XS_TEST中，把@@error=0回滚@@error <> 0回滚，事务执行成功是没有提示我事务计数不正确的；在存储过程XS_TEST中漏写commit后，会报错但发现t1并没有回滚，xt_a表中还是添加了一条记录，而第一次按代码执行报错并且t1回滚xt_a表并没有插入记录，两种错误情况有什么区别吗？
未求甚解，虚心请教。
问题点数：100、回复次数：10Top
1 楼cynthia（新鲜）回复于 2006-03-14 16:08:06 得分 0
自己先顶一下。Top
2 楼happyflystone（无枪的狙击手）回复于 2006-03-14 16:09:44 得分 100
CREATE PROCEDURE [XS_TEST] AS
DECLARE @ERR INT
SET @ERR = 0
BEGIN TRAN T1
UPDATE XT_b SET F_b = getdate()
if @@error = 0 -------此行为模拟事务回滚，改成了正确时回滚
begin
SET @ERR = -1
rollback
return @err --*****************
end
COMMIT TRAN T1
RETURN @ERR
GO
Top
3 楼cynthia（新鲜）回复于 2006-03-14 16:20:32 得分 0
楼上的，这个改了好像没有什么变化哦。感觉应该不是问题所在。Top
4 楼cynthia（新鲜）回复于 2006-03-14 16:21:51 得分 0
rollback后会跳出事务，但应该还是执行了return了的吧Top
5 楼cynthia（新鲜）回复于 2006-03-14 16:22:08 得分 0
rollback后会跳出事务，但应该还是执行了return了的吧Top
6 楼happyflystone（无枪的狙击手）回复于 2006-03-14 16:27:20 得分 0
drop table xt_a,xt_b
go
create table XT_A(f_a datetime)
create table XT_B(f_b datetime)
go
-----------
drop proc xs_test
go
CREATE PROCEDURE [XS_TEST] AS
DECLARE @ERR INT
SET @ERR = 0
--BEGIN TRAN
UPDATE XT_b SET F_b = getdate()
if @@error = 0 -------此行为模拟事务回滚，改成了正确时回滚
begin
SET @ERR = -1
-- rollback
return @err
end
--COMMIT TRAN T1
RETURN @ERR
GO
------------执行下列代码
declare @today datetime,@err int
set @today = getdate()
begin tran
insert xt_a values(@today)
if @@error<> 0
begin
rollback
goto finish
end
exec @err = XS_TEST
if @err <> 0
begin
rollback
goto finish
end
commit tran
finish:
select @err
select * from xt_a
Top
7 楼happyflystone（无枪的狙击手）回复于 2006-03-14 16:33:23 得分 0
我知道了，你是想写事务保存点吧!!
drop table xt_a,xt_b
go
create table XT_A(f_a datetime)
create table XT_B(f_b datetime)
go
-----------
drop proc xs_test
go
CREATE PROCEDURE [XS_TEST] AS
DECLARE @ERR INT
SET @ERR = 0
SAVE TRAN t
UPDATE XT_b SET F_b = getdate()
if @@error = 0 -------此行为模拟事务回滚，改成了正确时回滚
begin
SET @ERR = -1
ROLLBACK TRANSACTION t
return @err
end
COMMIT TRAN T1
RETURN @ERR
GO
------------执行下列代码
declare @today datetime,@err int
set @today = getdate()
begin tran t1
insert xt_a values(@today)
if @@error<> 0
begin
rollback
goto finish
end
begin tran t2
exec @err = XS_TEST
if @err <> 0
begin
rollback
goto finish
end
commit tran t1
commit tran t2
finish:
select @err
select * from xt_a
Top
8 楼happyflystone（无枪的狙击手）回复于 2006-03-14 16:34:29 得分 0
我知道了，你是想写事务保存点吧!!
drop table xt_a,xt_b
go
create table XT_A(f_a datetime)
create table XT_B(f_b datetime)
go
-----------
drop proc xs_test
go
CREATE PROCEDURE [XS_TEST] AS
DECLARE @ERR INT
SET @ERR = 0
SAVE TRAN t
UPDATE XT_b SET F_b = getdate()
if @@error = 0 -------此行为模拟事务回滚，改成了正确时回滚
begin
SET @ERR = -1
ROLLBACK TRANSACTION t
return @err
end
COMMIT TRAN t
RETURN @ERR
GO
------------执行下列代码
declare @today datetime,@err int
set @today = getdate()
begin tran t1
insert xt_a values(@today)
if @@error<> 0
begin
rollback
goto finish
end
begin tran t2
exec @err = XS_TEST
if @err <> 0
begin
rollback
goto finish
end
commit tran t1
commit tran t2
finish:
select @err
select * from xt_a
Top
9 楼happyflystone（无枪的狙击手）回复于 2006-03-14 16:41:56 得分 0
再修改一下，这下是成功的情况：
drop table xt_a,xt_b
go
create table XT_A(f_a datetime)
create table XT_B(f_b datetime)
go
-----------
drop proc xs_test
go
CREATE PROCEDURE [XS_TEST] AS
DECLARE @ERR INT
SET @ERR = 0
SAVE TRANSACTION t
insert XT_b values( '1909-01-01')
if @@error <> 0 -------此行为模拟事务回滚，改成了正确时回滚
begin
SET @ERR = -1
ROLLBACK TRANSACTION t
return @err
end
--COMMIT TRANSACTION T
RETURN @ERR
GO
------------执行下列代码
declare @today datetime,@err int
set @today = getdate()
begin tran t1
insert xt_a values(@today)
if @@error<> 0
begin
rollback
goto finish
end
begin tran t2
exec @err = XS_TEST
print 'OK'
if @err <> 0
begin
rollback
goto finish
end
commit tran t2
commit tran t1
finish:
select @err
select * from xt_a
select * from xt_bTop
10 楼cynthia（新鲜）回复于 2006-05-24 15:58:31 得分 0
结贴散分Top
