于康娟

(太原城市職業(yè)技術(shù)學(xué)院， 山西 太原030027)

前言

觸發(fā)器是一類特殊的存儲過程[1]，除了具備存儲過程的提前編譯、高效執(zhí)行、一旦創(chuàng)建即存在于數(shù)據(jù)庫服務(wù)器端，簡化業(yè)務(wù)邏輯維護(hù)，減少網(wǎng)絡(luò)流量等特點之外，還具備基于特定事件驅(qū)動的即時響應(yīng)和自動執(zhí)行的能力[2]，故在數(shù)據(jù)庫應(yīng)用程序開發(fā)中得到了廣泛應(yīng)用。

1 觸發(fā)器分類

1.1 DML觸發(fā)器

當(dāng)在數(shù)據(jù)表上執(zhí)行了INSERT，DELETE，UPDATE操作時，DML 觸發(fā)器被觸發(fā)，它被廣泛應(yīng)用于維護(hù)數(shù)據(jù)完整性和強(qiáng)制執(zhí)行一些業(yè)務(wù)規(guī)則。

1.1.1INSERT觸發(fā)器

INSERT觸發(fā)器在數(shù)據(jù)表中執(zhí)行INSERT操作時被觸發(fā)，系統(tǒng)會自動在內(nèi)存中創(chuàng)建INSERTED臨時表，它在結(jié)構(gòu)上和觸發(fā)器所在表的結(jié)構(gòu)相同，用來保存INSERT操作時數(shù)據(jù)的副本，觸發(fā)器執(zhí)行完成后，INSERTED表自動會被刪除[3]。

1.1.2DELETE觸發(fā)器

DELETE觸發(fā)器在數(shù)據(jù)表中執(zhí)行DELETE操作時被觸發(fā)，系統(tǒng)會自動在內(nèi)存中創(chuàng)建DELETED臨時表，它在結(jié)構(gòu)上和觸發(fā)器所在表的結(jié)構(gòu)相同，用來保存DELETE操作時數(shù)據(jù)的副本，觸發(fā)器執(zhí)行完成后，DELETED表自動會被刪除。

1.1.3UPDATE觸發(fā)器

UPDATE觸發(fā)器在數(shù)據(jù)表中執(zhí)行UPDATE操作時被觸發(fā)，系統(tǒng)會自動在內(nèi)存中創(chuàng)建INSERTED和DELETED兩個臨時表，它在結(jié)構(gòu)上和觸發(fā)器所在表的結(jié)構(gòu)相同，DELETED表用來保存UPDATE操作時數(shù)據(jù)修改前的副本，INSERTED用來保存UPDATE操作時數(shù)據(jù)修改后的副本，觸發(fā)器執(zhí)行完成后，INSERTED和DELETED表自動會被刪除。

1.1.4INSTEAD OF觸發(fā)器

INSTEAD OF觸發(fā)器可以定義在視圖上，替代INSERT、DELETE、UPDATE操作，完成通過視圖無法執(zhí)行的一些DML操作。

1.2 DDL 觸發(fā)器

DDL 觸發(fā)器是用來維護(hù)數(shù)據(jù)庫和服務(wù)器的安全性,對數(shù)據(jù)庫中的對象及服務(wù)器中的對象起到保護(hù)作用。當(dāng)服務(wù)器或數(shù)據(jù)庫中發(fā)生 DDL 事件時被觸發(fā)[4]。

2 觸發(fā)器的設(shè)計

對于觸發(fā)器的設(shè)計，應(yīng)結(jié)合具體的業(yè)務(wù)規(guī)則及數(shù)據(jù)的處理要求從以下幾方面來考慮：

1)明確創(chuàng)建哪種類型觸發(fā)器；

2)如果是DDL操作，確定是基于數(shù)據(jù)庫還是基于服務(wù)器；如果是DML操作，確定在哪些數(shù)據(jù)表中創(chuàng)建觸發(fā)器；

3)確定觸發(fā)時機(jī)及觸發(fā)事件；

4)編程實現(xiàn)觸發(fā)器的關(guān)聯(lián)動作；

5)最后考慮觸發(fā)器的安全性，是否需要加密。

3 觸發(fā)器的具體應(yīng)用

基于觸發(fā)器的自動執(zhí)行這一特點，它在各行各業(yè)后臺數(shù)據(jù)庫的維護(hù)中起到了重要作用，它對于多表數(shù)據(jù)的一致性維護(hù)、及時性更新以及添加權(quán)限的判斷方面得到了廣泛應(yīng)用。

3.1 利用觸發(fā)器維護(hù)數(shù)據(jù)的完整性

在學(xué)生成績管理系統(tǒng)中有學(xué)生信息表(T_Student)和成績表(T_Score)

結(jié)構(gòu)如下：

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

T_Score (S_number,C_number,Score)

假如有一名學(xué)生轉(zhuǎn)學(xué)，需要從學(xué)生信息表中刪除學(xué)生信息，那么學(xué)生成績表中的信息也要相應(yīng)做刪除，從而保證數(shù)據(jù)庫中數(shù)據(jù)一致性。

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 ′成績表有數(shù)據(jù)刪除！′

delete from T_score where S_number=@n

end

else

print ′該學(xué)生沒有選課，無成績信息！′

go

delete from T_student where S_name=′劉華′

3.2 利用觸發(fā)器實現(xiàn)普通約束所不能實現(xiàn)的數(shù)據(jù)完整性

在商品信息庫中有商品信息表和銷售信息表，結(jié)構(gòu)如下：

Goods (商品編號,商品名稱,生產(chǎn)廠商,進(jìn)貨價,零售價,庫存數(shù)量,進(jìn)貨時間,進(jìn)貨員工編號)

Sell(銷售編號,商品編號,銷售數(shù)量,售出時間,售貨員工編號)

假如每銷售一筆商品，需要自動完成庫存數(shù)量的統(tǒng)計，能及時反映最新庫存數(shù)量。

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=銷售數(shù)量,@goodno=商品編號from inserted

update Goods set庫存數(shù)量=庫存數(shù)量-@num where商品編號=@goodno

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

select * from Goods where 商品編號=@goodno

go

3.3 利用觸發(fā)器實現(xiàn)復(fù)雜的業(yè)務(wù)規(guī)則

在銀行數(shù)據(jù)業(yè)務(wù)管理數(shù)據(jù)庫中有銀行開戶卡信息表和客戶信譽(yù)度信息表，結(jié)構(gòu)如下：

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

userCred(customerID,CustomerCred)

銀行在給用戶辦理開戶業(yè)務(wù)時，需要檢查此客戶的信譽(yù)值，如果信譽(yù)值小于銀行規(guī)定的定數(shù)值，是要被拉入系統(tǒng)的黑名單的，此客戶開戶將會不成功。

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 ‘此用戶不滿足信譽(yù)要求，已經(jīng)被列入黑名單，開戶失?。?！’

rollback transaction

end

else

print ‘此用戶開戶成功！’

go

3.4 利用觸發(fā)器保護(hù)數(shù)據(jù)庫對象的安全性

利用觸發(fā)器保護(hù)數(shù)據(jù)庫中的數(shù)據(jù)表不被刪除。

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′你不能刪除數(shù)據(jù)表！′

rollback transaction

go

3.5 利用觸發(fā)器保護(hù)服務(wù)器的安全性

利用觸發(fā)器保護(hù)在服務(wù)器中不能隨意創(chuàng)建數(shù)據(jù)庫。

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 ′你不能創(chuàng)建數(shù)據(jù)庫！′

rollback transaction

go

4 結(jié)束語

本文對學(xué)生成績管理、商品信息管理、銀行數(shù)據(jù)業(yè)務(wù)管理系統(tǒng)中數(shù)據(jù)完整性約束及特定的業(yè)務(wù)規(guī)則進(jìn)行了較為深入地探討和分析，根據(jù)各應(yīng)用系統(tǒng)的特點和規(guī)律進(jìn)行了幾類典型觸發(fā)器設(shè)計，并通過SQL Server得以實現(xiàn)。由于觸發(fā)器的自動響應(yīng)，高效運(yùn)行的特點，使得它在數(shù)據(jù)庫應(yīng)用系統(tǒng)的設(shè)計中居于重要的地位，掌握開發(fā)觸發(fā)器的技術(shù)，編寫出高效率的觸發(fā)器邏輯，是數(shù)據(jù)庫應(yīng)用程序開發(fā)成功的重要保障[5]。