第十章 触发器.docVIP

PAGE PAGE 7 第十章 触发器 create trigger trig_transInfo on transinfo with encryption for insert sp_helptrigger transINfo sp_helptext trig_transInfo /*－建表*/ use master go if db_id(bankdb) is not null drop database bankdb go create database bankdb go use bankdb go --创建帐户信息表bank和交易表transInfo if exists(select * from sysobjects where name = bank) drop table bank go create table bank --帐户信息表 ( customerName char(8) not null,--顾客姓名 cardId char(10) not null, --卡号 currentMoney money not null --当前余额 ) go if exists(select * from sysobjects where name = transInfo) drop table transInfo go 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 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 bankdb go /*检测是否存在：触发器存放在系统表sysobjects中*/ if exists(select name from sysobjects where name = trig_transInfo) drop trigger trig_transInfo go /*创建INSERT触发器：在交易信息表transInfo上创建插入触发器*/ create trigger trig_transInfo on transInfo for insert as /*定义变量：用于临时存放插入的卡号、交易类型、交易金额等*/ declare @type char(4),@outMoney money,@mycardId char(10),@balance money /*从inserted临时表中获取插入的记录行信息：包括交易类型、卡号、交易金额--*/ 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 /*显示交易金额及余额*/ if @@error 0 begin print 交易失败！ rollback transaction retur