SQL Server觸法器的應(yīng)用

于康娟

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

前言

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

1 觸發(fā)器分類

1.1 DML觸發(fā)器

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

1.1.1INSERT觸發(fā)器

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

1.1.2DELETE觸發(fā)器

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

1.1.3UPDATE觸發(fā)器

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

1.1.4INSTEAD OF觸發(fā)器

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

1.2 DDL 觸發(fā)器

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

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

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

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

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

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

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

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

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

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

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

在學(xué)生成績(jī)管理系統(tǒng)中有學(xué)生信息表(T_Student)和成績(jī)表(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é)生成績(jī)表中的信息也要相應(yīng)做刪除，從而保證數(shù)據(jù)庫(kù)中數(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 ′成績(jī)表有數(shù)據(jù)刪除！′

delete from T_score where S_number=@n

end

else

print ′該學(xué)生沒(méi)有選課，無(wú)成績(jī)信息！′

go

delete from T_student where S_name=′劉華′

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

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

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

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

假如每銷售一筆商品，需要自動(dòng)完成庫(kù)存數(shù)量的統(tǒng)計(jì)，能及時(shí)反映最新庫(kù)存數(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=商品編號(hào)from inserted

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

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

select * from Goods where 商品編號(hào)=@goodno

go

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

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

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

userCred(customerID,CustomerCred)

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

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)被列入黑名單，開(kāi)戶失?。?！’

rollback transaction

end

else

print ‘此用戶開(kāi)戶成功！’

go

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

利用觸發(fā)器保護(hù)數(shù)據(jù)庫(kù)中的數(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ù)庫(kù)。

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ù)庫(kù)！′

rollback transaction

go

4 結(jié)束語(yǔ)

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