触发器
一、
触发器概念
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
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
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 '
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 )
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 )