Sql Server中触发器的典型应用

--建表
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

--添加约束:帐户余额不能少于1元,交易日期默认为当天日期
alter table bank
  add constraint CK_currentMoney check(currentMoney>=1)
alter table transInfo
  add constraint DF_transDate default(getDate()) for transDate

/*插入测试数据:张三,开户金额为800;李四开户金额为1*/
insert into bank values('张三','10010001',1000)
insert into bank values('李四','10010002',1)

--检查触发器是否存在:触发器放在sysobjects中
if exists(select * from sysobjects where name='trig_transInfo')
 drop trigger trig_transInfo
go
create Trigger trig_transInfo --向transInfo表中建立Insert触发器
  on transInfo
    for Insert
as
/*定义变量,用于临时存放插入的卡号,交易类型,交易金额等*/
declare @type char(4),@outMoney Money
declare @myCardID char(10),@balance Money
--从Insert 临时表中获取插入的记录行信息
select @type=transType,@outMoney=transMoney,@myCardID=cardID from inserted
/*根据交易类型是支取/存入,减少或增加帐户表(bank)中对应卡号的余额*/
if(@type='支取')
   update bank set currentMoney=currentMoney-@outMoney where cardID=@myCardID
else
  update bank set currentMoney=currentMoney+@outMoney where cardID=@myCardID

--显示交易金额及余额
print '交易成功! 交易金额:'+convert(varchar(20),@outMoney)
select @balance=currentMoney from bank where cardID=@myCardID
print '卡号:'+@myCardID+' 余额'+convert(varchar(20),@balance)
go

--测试触发器插入测试数据:张三取钱200,李四存钱5000
set nocount on  --不显示T-SQL语句影响的记录行数

insert into transInfo(cardID,transType,transMoney) values('10010001','支取',2000)
insert into transInfo(cardID,transType,transMoney) values('10010002','存入',50000)

select * from bank
select * from transInfo

/*在transInfo表中创建Delete触发器*/
if exists(select * from sysobjects where name='trig_delete_transInfo')
 drop trigger trig_delete_transInfo
go
 create trigger trig_delete_transInfo
   on transInfo
     for delete
 as
   print '开始数据备份,请稍后...'
    if not exists(select * from sysobjects where name='backupTable')
       select * into backupTable from deleted --创建表并把删除的数据存放到表backupTable中
     else
       insert into backupTable select * from deleted --向已存在的表添加删除的记录
   print '数据备份成功'
   select * from backupTable

GO

/*测试delete触发器*/
delete from transInfo

/*在bank表中创建Update触发器*/
if exists(select * from sysobjects where name='trig_update_bank')
 drop trigger trig_update_bank
  go
create trigger trig_update_bank
 on bank
   for update
as
 declare @beforeMoney Money,@afterMoney Money --定义变量
 select @beforeMoney=currentMoney from deleted --获取交易前的余额
 select @afterMoney=currentMoney from inserted --获取交易后的余额
 if abs(@afterMoney-@beforeMoney)>20000         --检查交易金额是否>20000,此时用了绝对值函数
   begin
     print '交易金额:'+convert(varchar(8),abs(@afterMoney-@beforeMoney))
     raiserror('每笔交易不能超过20000，交易失败',16,1)
     rollback transaction
   end
go

/*测试触发器，修改余额*/
update bank set currentMoney=currentMoney-20000 where cardID='10010001'

insert into transInfo(cardID,transType,transMoney) values('10010002','支取',2000)

select * from bank
select * from transInfo