触发器

 

 

触发器

一、             触发器概念

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)

 

分享到：

上一篇：存储过程

下一篇：ADO.NET