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
endcommit tran t1
commit tran t2finish:
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表并没有插入记录,两种错误情况有什么区别吗?未求甚解,虚心请教。
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
endcommit tran t1
commit tran t2finish:
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表并没有插入记录,两种错误情况有什么区别吗?未求甚解,虚心请教。
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
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
endcommit tran finish:
select @err
select * from xt_a
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
endcommit tran t1
commit tran t2finish:
select @err
select * from xt_a
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
endcommit tran t1
commit tran t2finish:
select @err
select * from xt_a
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
endcommit tran t2
commit tran t1finish:
select @err
select * from xt_a
select * from xt_b