触发器实例

 use bbsDB 
go 
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 
( 
customerName char(8) not null, --顾客姓名 
cardID char(10) not null, --卡号 
currentMoney money not null --当前余额 
) 

create table transInfo 
( 
cardID char(10) not null, --卡号 
transType char(4) not null, --交易类型 
transMoney money not null, --交易金额 
transDate datetime not null --交易日期 
) 
go 
alter table bank 
add constraint CK_currentMoney check(currentMoney>=1) 
alter table transInfo 
add constraint DF_transDate default(getDate()) for transDate 
go 
insert into bank values('张三', '1001 0001', 1000) 
insert into bank values('李四', '1001 0002', 1) 

insert into transInfo(cardID,transType,transMoney) values('1001 0001','支取',200) 

select * from bank 
select * from transInfo 
go 


/*---------------- 练习一 -------------------*/ 
use bbsDB 
go 
if exists(select name from sysobjects where name='trig_transInfo') 
drop trigger trig_transInfo 
go 
create trigger trig_transInfo 
on transInfo 
for insert 
as 
declare @cardID char(10),@transType char(4),@transMoney money,@balance int 
select @cardID=cardID,@transType=transType,@transMoney=transMoney from inserted 
select @balance=currentMoney from bank where cardID=@cardID 

if (@transType='支取') 
if (@balance<=@transMoney+1) 
raiserror ('您的余额不足！帐上余额 %d 元',16,1,@balance) 
else 
update bank set currentMoney=currentMoney-@transMoney where cardID=@cardID 
else 
update bank set currentMoney=currentMoney+@transMoney where cardID=@cardID 

if (@@error<>0) 
begin 
print '交易失败！' 
rollback transaction 
end 
else 
print '交易成功！' 

select @balance=currentMoney from bank where cardID=@cardID 
print '卡号：'+@cardID+' 余额：'+convert(varchar(15),@balance)+' 元' 
print '' 
go 

-- 测试 
set nocount on 
delete from transInfo 
print '------ 交易前的数据 -------' 
select * from bank 
select * from transInfo 
print '测试张三取钱 1500 元' 
insert into transInfo(cardID,transType,transMoney) 
values('1001 0001','支取',1500) 
go 

print '测试李四存钱 2000 元' 
insert into transInfo(cardID,transType,transMoney) 
values('1001 0002','存入',2000) 
print '------ 交易后的数据 -------' 
select * from bank 
select * from transInfo 
go