创建和测试触发器.sql

来自「QL SERVER 应用开发 银行ATM(取款机）系统 数据库设计 我在学校」· SQL 代码 · 共 50 行

use bankDB
GO
--创建触发器
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 @myBalance money
declare @outMoneys money
declare @myCardID char(19)
select @type=transType,@outMoneys=transMoney,@myCardID=cardID from inserted
select @myBalance=balance from cardInfo where cardID=@myCardID
if(@type='支取')
begin
  if(@myBalance<=@outMoneys+1)
  begin
  raiserror('交易失败!余额不足!',16,1)
  print '卡号 '+@myCardID+' 余额 '+convert(varchar(30),@myBalance)
  rollback tran
  end
else
  begin
  update cardInfo set balance=balance-@outMoneys where cardID=@myCardID
  print '交易成功!交易金额: '+ convert(varchar(30),@outMoneys)
  select @myBalance=balance from cardInfo where cardID=@myCardID
  print '卡号 '+@myCardID+' 余额 '+convert(varchar(30),@myBalance)
  end
end
else
begin
  update cardInfo set balance=balance+@outMoneys where cardID=@myCardID
  print '交易成功!交易金额: '+ convert(varchar(30),@outMoneys)
  select @myBalance=balance from cardInfo where cardID=@myCardID--重新查询出余额
  print '卡号 '+@myCardID+' 余额 '+convert(varchar(30),@myBalance)
end
GO

--测试触发器
declare @card char(19)
select @card=cardID from cardInfo inner join userInfo on cardInfo.customerID=userInfo.customerID where userInfo.customerName='张三'
insert into transInfo(transType,cardID,transMoney)values('支取',@card,1000)
GO
declare @cards char(19)
select @cards=cardID from cardInfo inner join userInfo on cardInfo.customerID=userInfo.customerID where userInfo.customerName='李四'
insert into transInfo(transType,cardID,transMoney)values('存入',@cards,200)
GO