客户信息表

卡号信息表



交易信息表


创建触发器trig_trans
1。如果交易类型是‘支取’，则应判断余额是否足够支取(余额-支取金额>=1)，否则提示余额不足,
如果支取成功，更新余额，减去支取的金额
2。如果是存入,则更新余额,加上存入的金额
3。交易结束后，提示交易信息：是否成功，目前余额是多少

以下是我写的，不知道哪里不对，大侠们请不吝指点
--创建触发器trig_trans
if exists(select * from sysobjects where name='trig_trans')
drop trigger trig_trans
go
create trigger trig_trans on transInfo for insert
as
--定义变量
declare @type char(4)
declare @transMoney money
declare @cardID char(10)
declare @balance money

select @type=transType,@transMoney=transMoney,@cardID=cardID from inserted
select @balance=balance from cardInfo where cardID=@cardID
--根据交易类型是支取/存入,减少或增加帐户表中对应卡号的余额
if (@type='支取') //还有我这里把两个if连起来写可不可以 if (@type='支取' and @balance-@transMoney>=1)
begin
if (@balance-@transMoney>=1)
begin
update cardInfo set balance=balance-@transMoney where cardID=@cardID
print '交易成功，余额为'+convert(varchar(10),(@balance-@transMoney))
end
else
begin
raiserror('交易失败!余额不足!',16,1)
rollback transAction
end
end
else
begin
update cardInfo set balance=balance+@transMoney where cardID=@cardID
print '交易成功,余额为'+convert(varchar(10),(@balance+@transMoney))
end
--测试触发器
insert into transInfo values(default,'1010 3576 1234 5678','支取',1000,null)
insert into transInfo values(default,'1010 3576 1212 1134','存入',200,null)
select * from userInfo
select * from cardInfo
select * from transInfo

[此贴子已经被作者于2006-8-6 14:38:38编辑过]

回滚那里通过全局变量@@error判断应该比较好,你试试,下面代码供参考.
-------------------------创建insert触发器-------------------------
if exists(select * from sysobjects where name='trig_trans')
drop trigger trig_trans
go
create trigger trig_trans
on transInfo
for insert
as
declare @type char(4),@outMoney money,@cardID char(19),@balance int,@errorsum int
set @errorsum=0
select @type=transType,@outMoney=transMoney ,@cardID=cardID from inserted
select @balance=balance from cardInfo where cardID=@cardID
if (@type='支取')

if (@balance<=@outMoney+1)
raiserror('您的余额不足!帐上余额 %d元',16,1,@balance)
else
begin
update cardInfo set balance=balance-@outMoney where cardID=@cardID
end
else
begin
update cardInfo set balance=balance+@outMoney where cardID=@cardID
end
if @@error<>0
begin
print'交易失败!'
rollback tran
end
else
print'交易成功!'
select @balance=balance from cardInfo where cardID=@cardID
print '卡号'+@cardID+' 余额：'+convert(varchar(20),@balance)+'元'
go
--测试触发器
select * from userInfo
select * from transInfo
select * from cardInfo
declare @card char(19)
select @card=cardID from cardInfo Inner Join userInfo
on cardInfo.customerID=userInfo.customerID where cardID='1010 3576 1234 5678'
insert into transInfo(transType,cardID,transMoney) values('支取',@card,1000)
go
declare @card char(19)
select @card=cardID from cardInfo Inner Join userInfo
on cardInfo.customerID=userInfo.customerID where PID='321245678912345678'
insert into transInfo(transType,cardID,transMoney) values('存入',@card,200)
go
select * from transInfo
select * from cardInfo

谢谢楼上的，已经解决了

搂住那个学校的？