SQL Server2008 触发器详解

触发器
一、             触发器概念
SQL Server提供了两种主要机制来强制业务规则和数据完整性：约束和触发器。
 
触发器是在对表进行插入、更新或删除操作时自动执行的存储过程。
触发器通常用于强制业务规则，它是一种高级约束。比check更严格。
触发器主要是通过事件触发而被执行的。
 
触发器的作用：
1数据的完整性和一致性（主要作用）。
2 强化约束
3 跟踪变化
4 级联运行
 
 
二、             触发器分类
触发器分为以下几种：
1 insert触发器：当向表中插入数据时触发，自动执行触发器所定义的SQL语句。
inserted表存放新增的记录。
 
2 delete触发器：当更新表中某列、多列时触发，自动执行触发器所定义的SQL语句。deleted表存放被删除的记录。
 
3 update触发器：当删除表中记录时触发，自动执行触发器所定义的SQL语句。
inserted表存放用来更新的新记录。
deleted表存放更新前的记录。
 
 
三、             触发器语法
create trigger trigger_name
on <table_name|view_name>
    [with encryption]
    for{[delete,insert,update(字段名)]}
    as
        if delete,insert,update(字段名)]
 
       SQL语句
 
其中:
trigger_name:触发器的名称.
[delete,insert,update]:在其上执行触发器的表或视图.
with encryption:加密syscomments表中包含create trigger语句文本的条目.
[delete,insert,update]:指在表或视图上执行哪些数据修改语句时将激活触发器的关健字.多个用逗号隔开.
update(字段名):指出哪个表或视图上的[字段名]被更新时触发
       
         如:
            create trigger trig_test
            on test
                with encryption
                for delete
                    as
                        if delete,insert,update(字段名)
 
 
四、             创建insert触发器
当向交易信息表(transInfo)中插入一条交易信息时，应自动更新对应帐户的余额。
              在交易信息表上创建insert触发器。
use stuDB
go
if exists(select name from sysobjects where name='trig_insert_transInfo')
    drop trigger trig_insert_transInfo
go
/**---创建insert触发器---**/
create trigger trig_insert_transInfo
 on transInfo
 for insert
    as
       declare @myCardID char(10)      --卡号
       declare @type char(4)           --交易类型
       declare @outMoney money         --交易金额
 
       declare @balance money          --余额
       /**--从inserted临时表中获取插入的记录信息--**/
       select @myCardID=cardID,@type=transType,@outMoney=transMoney from inserted
       /**--根据交易类型计算余额--**/
       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+' 余额: '+cast(@balance as varchar(20))
go
 
/*--测试--*/
set nocount on
insert into transInfo(cardID,transType,transMoney) values('10010001','支取',200)
insert into transInfo(cardID,transType,transMoney) values('10010002','存入',5000)
 
--查看结果
select * from bank
select * from transInfo
 
五、             创建delete触发器
其典型应用是银行系统中的数据备份，当交易记录过多时，为了不影响数据访问速度，交易信息表需要定期删除部分数据。当删除数据时，一般需要自动备份，以方便将来的客户查询、数据恢复和年终统计等。
 
当删除交易信息表（transInfo）时，自动备份被删除的数据到表backupTable表中。
use stuDB
go
if exists(select name from sysobjects where name='trig_delete_transInfo')
    drop trigger trig_delete_transInfo
go
/**---创建insert触发器---**/
create trigger trig_delete_transInfo
 on transInfo
 for delete
    as
       print '开始备份数据,请稍候......'
       if not exists(select * from sysobjects where name='backupTable')
           /*--从deleted表中获取被删除的数据--*/
           select * into backupTable from deleted
       else
           insert into backupTable select * from deleted
 
       print '备份数据成功,备份表中的数据为:'
       select * from backupTable
go
 
/*--测试--*/
set nocount on
delete transInfo
 
六、             创建update触发器
其主要应用于跟踪数据的变化。典型的应用就是银行系统，为了安全起见，一般要求每次交易金额不能超过一定的数额。
 
跟踪用户的交易，交易金额超过20000元，则取消交易，并给出错误提示。
use stuDB
go
if exists(select name from sysobjects where name='trig_update_transInfo')
    drop trigger trig_update_transInfo
go
/**---创建insert触发器---**/
create trigger trig_update_transInfo
 on bank
 for update
    as
       declare @beforeMoney money --交易前的余额
       declare @afterMoney money   --交易后的余额
      
       select @beforeMoney=currentMoney from deleted --获取交易前的余额
       select @afterMoney=currentMoney from inserted --获取交易后的余额
      
       if abs(@afterMoney-@beforeMoney)>20000
           begin
              print '交易金额:'+cast(abs(@afterMoney-@beforeMoney) as varchar(8))
              raiserror('每笔交易金额不得超过万元,交易失败!',16,1)
              rollback transaction --回滚事务,撤消操作.
           end
go
 
/*测试*/
set nocount on
update bank set currentMoney=currentMoney+25000 where cardID='10010001'
 
 
七、             本节触发器测试用数据库:
 
use master
execute xp_cmdshell 'mkdir d:stu',no_output
go
/*建立数据库stuDB*/
if exists(select * from sysdatabases where name='stuDB')
    drop database stuDB
create database stuDB
on primary
(
    name='stuDB_data',
    filename='d:stustuDB_data.mdf',
    size=3mb,
    maxsize=100mb,
    filegrowth=2%
)
log on
(
    name='stuDB_log',
    filename='d:stustuDB_log.ldf',
    size=1mb,
    maxsize=50mb,
    filegrowth=1
)
go
 
use stuDB
go
/*---创建帐户信息表bank---*/
if exists(select * from sysobjects where name='bank')
    drop table bank
create table bank --帐户信息表
(
    customerName char(8) not null, --顾客姓名
    cardID char(10) not null,       --卡号
    currentMoney money not null     --当前余额
)  
go
/*---创建交易信息表transInfo---*/
if exists(select * from sysobjects where name='transInfo')
    drop table transInfo
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(customerName,cardID,currentMoney) values('张三','10010001',1000)
insert into bank(customerName,cardID,currentMoney) values('李四','10010002',1)