SQL触发器银行交易使用实例

 创建INSERT触发器:问题:当我们向交易信息表stansinfo中插入一条交易信息时,应自动更新对应帐户的余额.use studbgo/*--------检测是否存在,触发器存放在系统表sysobjects中--------*/

if exists (select name from sysobjects where name='trig_transinfo')drop trigger trig_transinfogo/*--------创建INSERT触发器:在交易信息表transinfo上创建插入触发器--------*/

create trigger trig_transinfo on transinfo for insert as /*----定义变量:用于临时存放插入的卡号,交易类型,交易金额等---*/

declare @type char(4),@outmoney money declare @mycardid char(10),@banlance money /*--从inserted临时表中获取插入的记录行信息:包括交易类型,卡号,交易金额--*/ select @type=transtype,@outmoney=transmoney,@mycardid=cardid from inserted /*--根据交易类型是支取/存入,减少或增加帐户表band中对应卡号的余额--*/

if(@type='支取') update band set currentmoney=currentmoney-@outmoney where cardid=@mycardid else update band set currentmoney=currentmoney+@outmoney where cardid=@mycardid /*--显示交易金额及余额--*/

print '交易成功!交易金额:'+convert(varchar(20),@outmoney) select @balance=currentmoney from band where cardid=@mycardid print '卡号:'+@mycardid+'余额:'+convert(varchar(20),@balance)go/*

--测试触发器插入测试数据:张三取钱200,李四存钱50000--*/

--delete from transinfo

set nocount oninsert into stansinfo(cardid,transtype,transmoney)values('10010001','支取',200)insert into stansinfo(cardid,transtype,transmoney)values('10010002','存入',50000)

--查看结果

select * from bankselect * from transinfo

---

创建DELETE触发器:当删除交易信息表时,自动备份被删除的数据到表backuptable中.

use studbgo

if exists (select name 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 delete

 --从deleted表中获取被删除的数据

 else

 insert into banckuptable select * from deleted

print '备份数据成功,备份表中的数据为:'

select * from backuptable

go

---

创建UPDATE触发器:跟踪用户的交易,交易金额超过2000元,则取消交易,并给出错误提示.

use studb

go

if exists (select name form systojects where name='trig_update_bank')

drop trigger trig_update_bank

go

create trigger trig_update_bankon bank

for update

as

declare @beforemoney money,@aftermoney money

select @beroremoney=currentmoney from deleted

select @aftermoney=currentmoney from inserted

if abs(@aftermoney-@beforemoney)>20000 --abs 为取绝对值

begin

print '交易金额:'+convert(varchar(8),abs(@aftermoney-@beforemoney))

raiserror('每笔交易不能超过2万元,交易失败',16,1)

rollback transaction --回滚事务,撤销交易

end

go

/*--测试触发器:修改余额--*/

set nocount on

update bank set currentmoney=currentmoney+2500 where cartid='10010001'

go

insert into rtansinfo(cardid,transtype,transmoney) values('10010002','支取',30000)

insert into rtansinfo(cardid,transtype,transmoney) values('10010002','存入',5000)

go

/*--查看结果--*/

print '帐户信息表中的数据:'

select * from bank

print '交易作息表的数据:'

select * from transinfo

示例5:交易日期一般由系统自动产生,默认为当前日期,为了安全起见,一般禁止修改,以防舞弊.

use studb

go

if exists (select name from sysobjects where name='trig_update_transinfo')

drop trigger trig_update_transinfo

go

create trigger trig_update_transinfoon transinfofor updateasif update(transdate) --检查是否修改了交易日期列

transdatebeginprint '交易失败....'

raiserror('安全警告:交易日期不能修改,由系统自动产生',16,1)

rollback transactionendgo

/*--测试触发器,修改交易日期--*/

set nocount onupdate transinfo set transdate='2000-1-1'go