以下是书上面的一个例子,我在sql 2008上执行是事务回滚但在sql 2000 上执行的提示错误,但事务却提交了!
这个是模拟银行转帐的例子,有个约束是帐户内余额要>=1!
use GW_0526
go
set nocount on
if exists (select * from sysobjects where name='bank')drop table bank
if exists (select * from sysobjects where name='transinfo')
drop table transinfo
go
create table bank
(
name char(8) not null,
CardID char(10) not null,
CMoney money not null,
)
go
create table transinfo
(
Cardid char(10) not null,
Ttype char(4) not null,
TMoney money not null,
Tdate datetime not null,
)
go
alter table bank
add constraint CK_Cmoney check(Cmoney>=1)
alter table transinfo
add constraint CK_Ttype check (Ttype in ('存入','支出'))
goinsert into bank (name,CardID,CMoney)
select '张三','10010001',1000 union
select '李四','10010002',1
print '------------转帐前的余额------------'
select * from bank
select * from transinfo
go
Begin tran
declare @zmoney int, @error int
set @zmoney=1000
set @error=0
insert into transinfo(Cardid,Ttype,TMoney,Tdate)
values('10010001','支出',@zmoney,GETDATE())
set @error=@error+@@ERROR
update bank set CMoney=CMoney-10000 where CardID='10010001'
set @error=@error+@@ERROR
print @error
insert into transinfo(Cardid,Ttype,TMoney,Tdate)
values('10010002','存入',@zmoney,GETDATE())
set @error=@error+@@ERROR
update bank set CMoney=CMoney+@zmoney where CardID='10010002'
set @error=@error+@@ERROR
print'-------------转帐中的帐户余额和交易情况----------'
select * from bank
select * from transinfo
if @error<>0
begin
print '交易失败'
rollback tran
end
else
begin
print '交易成功'
commit tran
end
go
print '--------交易后的帐户情况---------'
select * from bank
select * from transinfo
go请各位指点下,到底是错在哪里? 谢谢!
这个是模拟银行转帐的例子,有个约束是帐户内余额要>=1!
use GW_0526
go
set nocount on
if exists (select * from sysobjects where name='bank')drop table bank
if exists (select * from sysobjects where name='transinfo')
drop table transinfo
go
create table bank
(
name char(8) not null,
CardID char(10) not null,
CMoney money not null,
)
go
create table transinfo
(
Cardid char(10) not null,
Ttype char(4) not null,
TMoney money not null,
Tdate datetime not null,
)
go
alter table bank
add constraint CK_Cmoney check(Cmoney>=1)
alter table transinfo
add constraint CK_Ttype check (Ttype in ('存入','支出'))
goinsert into bank (name,CardID,CMoney)
select '张三','10010001',1000 union
select '李四','10010002',1
print '------------转帐前的余额------------'
select * from bank
select * from transinfo
go
Begin tran
declare @zmoney int, @error int
set @zmoney=1000
set @error=0
insert into transinfo(Cardid,Ttype,TMoney,Tdate)
values('10010001','支出',@zmoney,GETDATE())
set @error=@error+@@ERROR
update bank set CMoney=CMoney-10000 where CardID='10010001'
set @error=@error+@@ERROR
print @error
insert into transinfo(Cardid,Ttype,TMoney,Tdate)
values('10010002','存入',@zmoney,GETDATE())
set @error=@error+@@ERROR
update bank set CMoney=CMoney+@zmoney where CardID='10010002'
set @error=@error+@@ERROR
print'-------------转帐中的帐户余额和交易情况----------'
select * from bank
select * from transinfo
if @error<>0
begin
print '交易失败'
rollback tran
end
else
begin
print '交易成功'
commit tran
end
go
print '--------交易后的帐户情况---------'
select * from bank
select * from transinfo
go请各位指点下,到底是错在哪里? 谢谢!
Begin tran
declare @zmoney int, @error int
set @zmoney=1000
insert into transinfo(Cardid,Ttype,TMoney,Tdate)
values('10010001','支出',@zmoney,GETDATE())
if @@ERROR <>0 goto error
update bank set CMoney=CMoney-10000 where CardID='10010001'
if @@ERROR <>0 goto error
insert into transinfo(Cardid,Ttype,TMoney,Tdate)
values('10010002','存入',@zmoney,GETDATE())
if @@ERROR <>0 goto error
update bank set CMoney=CMoney+@zmoney where CardID='10010002'
if @@ERROR <>0 goto error
else
begin
print '交易成功'
print '--------交易后的帐户情况---------'
select * from bank
select * from transinfo
commit tran
end
error:
begin
print '交易失败'
rollback tran
end
go
values('10010001','支出',@zmoney,GETDATE())
set @error=@error+@@ERROR
update bank set CMoney=CMoney-10000 where CardID='10010001'
set @error=@error+@@ERROR update的时候应该会报错啊,这是SQL 2005下执行的结果
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint "CK_Cmoney". The conflict occurred in database "AdventureWorks", table "dbo.bank", column 'CMoney'.
The statement has been terminated.下面是SQL 2000下执行的结果:
Server: Msg 547, Level 16, State 1, Line 1
UPDATE statement conflicted with COLUMN CHECK constraint 'CK_Cmoney'. The conflict occurred in database 'tempdb', table 'bank', column 'CMoney'.
The statement has been terminated.怎么会提交呢?
你按3楼的来,应该没问题。