SQL Server触法器的应用

于康娟

(太原城市职业技术学院， 山西 太原030027)

前言

触发器是一类特殊的存储过程[1]，除了具备存储过程的提前编译、高效执行、一旦创建即存在于数据库服务器端，简化业务逻辑维护，减少网络流量等特点之外，还具备基于特定事件驱动的即时响应和自动执行的能力[2]，故在数据库应用程序开发中得到了广泛应用。

1 触发器分类

1.1 DML触发器

当在数据表上执行了INSERT，DELETE，UPDATE操作时，DML 触发器被触发，它被广泛应用于维护数据完整性和强制执行一些业务规则。

1.1.1INSERT触发器

INSERT触发器在数据表中执行INSERT操作时被触发，系统会自动在内存中创建INSERTED临时表，它在结构上和触发器所在表的结构相同，用来保存INSERT操作时数据的副本，触发器执行完成后，INSERTED表自动会被删除[3]。

1.1.2DELETE触发器

DELETE触发器在数据表中执行DELETE操作时被触发，系统会自动在内存中创建DELETED临时表，它在结构上和触发器所在表的结构相同，用来保存DELETE操作时数据的副本，触发器执行完成后，DELETED表自动会被删除。

1.1.3UPDATE触发器

UPDATE触发器在数据表中执行UPDATE操作时被触发，系统会自动在内存中创建INSERTED和DELETED两个临时表，它在结构上和触发器所在表的结构相同，DELETED表用来保存UPDATE操作时数据修改前的副本，INSERTED用来保存UPDATE操作时数据修改后的副本，触发器执行完成后，INSERTED和DELETED表自动会被删除。

1.1.4INSTEAD OF触发器

INSTEAD OF触发器可以定义在视图上，替代INSERT、DELETE、UPDATE操作，完成通过视图无法执行的一些DML操作。

1.2 DDL 触发器

DDL 触发器是用来维护数据库和服务器的安全性,对数据库中的对象及服务器中的对象起到保护作用。当服务器或数据库中发生 DDL 事件时被触发[4]。

2 触发器的设计

对于触发器的设计，应结合具体的业务规则及数据的处理要求从以下几方面来考虑：

1)明确创建哪种类型触发器；

2)如果是DDL操作，确定是基于数据库还是基于服务器；如果是DML操作，确定在哪些数据表中创建触发器；

3)确定触发时机及触发事件；

4)编程实现触发器的关联动作；

5)最后考虑触发器的安全性，是否需要加密。

3 触发器的具体应用

基于触发器的自动执行这一特点，它在各行各业后台数据库的维护中起到了重要作用，它对于多表数据的一致性维护、及时性更新以及添加权限的判断方面得到了广泛应用。

3.1 利用触发器维护数据的完整性

在学生成绩管理系统中有学生信息表(T_Student)和成绩表(T_Score)

结构如下：

T_Student(S_number,S_name,Sex,Birthday,Nation,Politics,Department，PostalCode,Address,Phone)

T_Score (S_number,C_number,Score)

假如有一名学生转学，需要从学生信息表中删除学生信息，那么学生成绩表中的信息也要相应做删除，从而保证数据库中数据一致性。

if exists(select * from sys.triggers where name=′tr_student_delete′)

drop trigger tr_student_delete

go

create trigger tr_student_delete

on T_student

with encryption

for delete

as

declare @n int,@stuno varchar(8)

select @stuno=(select S_number from deleted)

select @n=count(*) from T_Score where S_number=@stuno

if @n<>0

begin

print ′成绩表有数据删除！′

delete from T_score where S_number=@n

end

else

print ′该学生没有选课，无成绩信息！′

go

delete from T_student where S_name=′刘华′

3.2 利用触发器实现普通约束所不能实现的数据完整性

在商品信息库中有商品信息表和销售信息表，结构如下：

Goods (商品编号,商品名称,生产厂商,进货价,零售价,库存数量,进货时间,进货员工编号)

Sell(销售编号,商品编号,销售数量,售出时间,售货员工编号)

假如每销售一笔商品，需要自动完成库存数量的统计，能及时反映最新库存数量。

if exists(select * from sys.triggers where name=′tr_ Sell _insert′)

drop trigger tr_ Sell _insert

go

create trigger tr_ Sell _insert

on Sell

with encryption

for insert

as

declare @num int,@goodno varchar(3)

select @num=销售数量,@goodno=商品编号from inserted

update Goods set库存数量=库存数量-@num where商品编号=@goodno

print @goodno +‘最新库存量信息如下:’

select * from Goods where 商品编号=@goodno

go

3.3 利用触发器实现复杂的业务规则

在银行数据业务管理数据库中有银行开户卡信息表和客户信誉度信息表，结构如下：

userInfo (customerID,customerName,PID,telephone,address)

userCred(customerID,CustomerCred)

银行在给用户办理开户业务时，需要检查此客户的信誉值，如果信誉值小于银行规定的定数值，是要被拉入系统的黑名单的，此客户开户将会不成功。

if exists(select * from sys.triggers where name=′tr_ userInfo _insert′)

drop trigger tr_ userInfo _insert′

go

create trigger tr_ userInfo _insert′on Sell

with encryption

for insert

as

declare @cred int

select @cred= CustomerCred from userCred where customerID=( select customerID from inserted)

if @cred<1

begin

print ‘此用户不满足信誉要求，已经被列入黑名单，开户失败！！’

rollback transaction

end

else

print ‘此用户开户成功！’

go

3.4 利用触发器保护数据库对象的安全性

利用触发器保护数据库中的数据表不被删除。

if exists(select * from sys.triggers where name=′tr_drop_table′)

drop trigger tr_drop_table on database

go

create trigger tr_drop_table

on database

for drop_table

as

print′你不能删除数据表！′

rollback transaction

go

3.5 利用触发器保护服务器的安全性

利用触发器保护在服务器中不能随意创建数据库。

if exists(select * from sys.server_triggers where name=′tr_create_database′)

drop trigger tr_create_database on all server

go

create trigger tr_createdatabase

on all server

for create_database

as

print ′你不能创建数据库！′

rollback transaction

go

4 结束语

本文对学生成绩管理、商品信息管理、银行数据业务管理系统中数据完整性约束及特定的业务规则进行了较为深入地探讨和分析，根据各应用系统的特点和规律进行了几类典型触发器设计，并通过SQL Server得以实现。由于触发器的自动响应，高效运行的特点，使得它在数据库应用系统的设计中居于重要的地位，掌握开发触发器的技术，编写出高效率的触发器逻辑，是数据库应用程序开发成功的重要保障[5]。