SQL觸發(fā)器在系統(tǒng)開(kāi)發(fā)中的應(yīng)用

1、SQL觸發(fā)器在系統(tǒng)開(kāi)發(fā)中的應(yīng)用摘要：數(shù)據(jù)的完整性和安全性是衡量數(shù)據(jù)庫(kù)性能的重要標(biāo)準(zhǔn)。系統(tǒng)開(kāi)發(fā)過(guò)程中實(shí)現(xiàn)數(shù)據(jù)安全性和完整性的方法有很多，文章從觸發(fā)器角度來(lái)分析如何實(shí)現(xiàn)數(shù)據(jù)的安全性和完整性。關(guān)鍵詞：觸發(fā)器，完整性，安全性1.觸發(fā)器的概述觸發(fā)器是數(shù)據(jù)庫(kù)軟件中一個(gè)重要工具，通過(guò)觸發(fā)器可以完成數(shù)據(jù)表復(fù)雜的數(shù)據(jù)完整性約束，以防止不正確的操作。 觸發(fā)器是存儲(chǔ)過(guò)程的一種特殊類型。觸發(fā)器可視作表的一部分，與數(shù)據(jù)庫(kù)中的表緊密關(guān)聯(lián)，當(dāng)數(shù)據(jù)表有相關(guān)的事件發(fā)生時(shí)，觸發(fā)器會(huì)自動(dòng)觸發(fā)執(zhí)行相關(guān)操作，從而保證數(shù)據(jù)的一致性和完整性。SQL Server觸發(fā)器可分為DML 觸發(fā)器和 DDL 觸發(fā)器。DDL 觸發(fā)器會(huì)在發(fā)生數(shù)據(jù)定義語(yǔ)

2、言（DDL）事件時(shí)觸發(fā)；DML 觸發(fā)器是指當(dāng)數(shù)據(jù)庫(kù)中發(fā)生數(shù)據(jù)操作語(yǔ)言（DML）事件時(shí)觸發(fā)。DML觸發(fā)器有三種類型：后（AFTER ）觸發(fā)器、替代（INSTEAD OF ）觸發(fā)器和CLR 觸發(fā)器。通常執(zhí)行了 Insert、Update 或 Delete 語(yǔ)句操作之后將會(huì)執(zhí)行后（AFTER ）觸發(fā)器。當(dāng)觸發(fā)器被激活時(shí)，系統(tǒng)自動(dòng)創(chuàng)建兩個(gè)臨時(shí)表：delete臨時(shí)表和inserted臨時(shí)表。delete臨時(shí)表存放執(zhí)行delete或update語(yǔ)句后，要從源表中刪除的行。inserted臨時(shí)表存放執(zhí)行insert或update語(yǔ)句后，要在源表中插入的新行。文章以AFTER觸發(fā)器為例來(lái)講述觸發(fā)器在保持?jǐn)?shù)據(jù)完

3、整性中的應(yīng)用。2.用觸發(fā)器提高數(shù)據(jù)庫(kù)的安全性觸發(fā)器可以基于時(shí)間這個(gè)條件限制用戶的某些操作，比如不允許在工作時(shí)間之外或節(jié)假日修改數(shù)據(jù)庫(kù)中的數(shù)據(jù)。觸發(fā)器可以基于數(shù)據(jù)庫(kù)中的數(shù)據(jù)限制用戶的重要操作，如股票每天的升幅不允許超過(guò)10%等。還可以將信息檢索系統(tǒng)數(shù)據(jù)庫(kù)中的記錄設(shè)為“只讀”，在一定程度上對(duì)相關(guān)的數(shù)據(jù)庫(kù)起到保護(hù)作用。如在很多情況下，數(shù)據(jù)庫(kù)僅僅是為了提供信息檢索等應(yīng)用而創(chuàng)建的，幾乎不作修改，例如一些查詢系統(tǒng)。可以通過(guò)為用戶設(shè)定指定的Select 權(quán)限，來(lái)保證數(shù)據(jù)庫(kù)中的數(shù)據(jù)不被修改，從而在一定程度上對(duì)數(shù)據(jù)庫(kù)起到一定的保護(hù)作用，而這種方法不能阻止通過(guò)緩存區(qū)溢出等一些攻擊手段提升權(quán)限后進(jìn)行非法的修改或刪除

4、操作。于是將信息檢索系統(tǒng)數(shù)據(jù)庫(kù)中的記錄設(shè)為“只讀”就非常有必要了。應(yīng)用觸發(fā)器把對(duì)表記錄的更改操作全部的屏蔽掉，從而可以準(zhǔn)確地記錄企圖篡改數(shù)據(jù)庫(kù)記錄的相關(guān)非法操作。此類觸發(fā)器設(shè)計(jì)方法如下：createtrigger 觸發(fā)器名 on 表名instead of update,insert,deleteasprint 禁止修改表!此處為日志記錄需做相關(guān)的工作 Go應(yīng)用該觸發(fā)器的數(shù)據(jù)庫(kù)，無(wú)論是誰(shuí)在對(duì)表執(zhí)行插入、刪除、更新操作時(shí)，就會(huì)激活該觸發(fā)器，即使是非法的用戶提升權(quán)限，也不能去修改表中的相關(guān)記錄。這種將表設(shè)為“只讀”的方法，管理員和用戶的所有修改更新操作被屏蔽了，從而有效地保護(hù)了數(shù)據(jù)記錄。但是如果數(shù)據(jù)

5、庫(kù)中的數(shù)據(jù)需要進(jìn)行更新時(shí)，就必須先刪除該類觸發(fā)器，從而給管理員的維護(hù)帶來(lái)許多不便。所以在使用該方法時(shí)，數(shù)據(jù)庫(kù)管理員針對(duì)于不同的應(yīng)用場(chǎng)合，設(shè)計(jì)一種不同的安全管理機(jī)制。3.用觸發(fā)器實(shí)現(xiàn)復(fù)雜完整性規(guī)則SQL Server用約束和觸發(fā)器兩種方式來(lái)強(qiáng)制使用業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性。觸發(fā)器作為特殊類型的存儲(chǔ)過(guò)程，可在執(zhí)行相關(guān)的語(yǔ)言事件時(shí)自動(dòng)觸發(fā)執(zhí)行。DML觸發(fā)器可以查詢其他表，還可以包含復(fù)雜的T-SQL語(yǔ)句，可將觸發(fā)器作為事務(wù)來(lái)對(duì)待。觸發(fā)器在系統(tǒng)設(shè)計(jì)過(guò)程中具有以下幾方面的作用。(1).通過(guò)DML觸發(fā)器實(shí)現(xiàn)數(shù)據(jù)庫(kù)中的相關(guān)表之間的級(jí)聯(lián)更新、刪除觸發(fā)器可以對(duì)數(shù)據(jù)庫(kù)中相關(guān)表之間的數(shù)據(jù)進(jìn)行級(jí)聯(lián)的更新、刪除或者計(jì)算。如在

6、更新或刪除表中記錄時(shí)級(jí)聯(lián)更新或刪除與之關(guān)聯(lián)表中的相匹配的行。在更新或刪除時(shí)把之關(guān)聯(lián)表中的相關(guān)行設(shè)成NULL值或設(shè)成缺省值。例如有如下兩個(gè)關(guān)系：菜單（菜品編號(hào)，菜品名稱，規(guī)格，類別，單價(jià)，成本價(jià)），點(diǎn)菜（訂單編號(hào)，菜品編號(hào)，數(shù)量）。以級(jí)聯(lián)更新為例，分析當(dāng)菜單表中的菜品編號(hào)修改時(shí)，點(diǎn)菜表中的菜品編號(hào)同時(shí)更新這樣的操作用觸發(fā)器如何實(shí)現(xiàn)：create trigger 觸發(fā)器名 on 菜單 after updateasdeclarejbhchar(5), xbhchar(5)select jbh=deleted.菜品編號(hào), xbh= inserted. 菜品編號(hào)fromdeleted, inserted

7、 where deleted. 菜品編號(hào)= inserted. 菜品編號(hào)print 準(zhǔn)備級(jí)聯(lián)更新點(diǎn)菜表中的菜品編號(hào)信息.Update 點(diǎn)菜 set 菜品編號(hào)=xbh where 菜品編號(hào)=jbh print 已經(jīng)級(jí)聯(lián)更新了點(diǎn)菜表原菜品編號(hào)為+ jbh +的信息(2).用觸發(fā)器設(shè)計(jì)比 CHECK 約束更為復(fù)雜的限制約束和 DML 觸發(fā)器在不同的情況下有其各自的優(yōu)點(diǎn)。DML 觸發(fā)器的優(yōu)點(diǎn)是它可以用T-SQL 代碼處理復(fù)雜處理邏輯，它可以支持CHECK約束的所有功能；但對(duì)于有些復(fù)雜的規(guī)則CHECK約束是不能實(shí)現(xiàn)的。比如當(dāng)用戶對(duì)表進(jìn)行了相應(yīng)的操作后，希望系統(tǒng)能自動(dòng)撤消該操作，并給出用戶提示信息，這樣的

8、操作對(duì)于CHECK 約束是不能實(shí)現(xiàn)的。對(duì)于這種實(shí)現(xiàn)自動(dòng)撤銷的操作可用觸發(fā)器來(lái)實(shí)現(xiàn)。例如在3.1中所述的菜單表上創(chuàng)建的觸發(fā)器，當(dāng)對(duì)該表的“菜單名稱”列修改時(shí)激發(fā)該觸發(fā)器，使用戶不能修改“菜單名稱”列，并進(jìn)行回滾。其方法如下：create trigger觸發(fā)器名 on 菜單 after updateasif update(菜單名稱)beginrollback transaction raiserror(不能修改菜單名稱！, 16 ,1)end另外觸發(fā)器還可以自動(dòng)計(jì)算數(shù)據(jù)值，比如當(dāng)數(shù)據(jù)庫(kù)中某一數(shù)據(jù)的值達(dá)到了報(bào)警的要求時(shí)，觸發(fā)器會(huì)立即給相關(guān)人員發(fā)送警告。(3).用觸發(fā)器實(shí)現(xiàn)外鍵約束外鍵約束用來(lái)實(shí)現(xiàn)數(shù)據(jù)

9、的參照完整性，它可以在表創(chuàng)建或修改時(shí)定義，也可以通過(guò)觸發(fā)器來(lái)實(shí)現(xiàn)。如上述的菜單表和和點(diǎn)菜表就存在著參照的關(guān)系，點(diǎn)菜表的菜單編號(hào)必須在菜單表中存在才行，即點(diǎn)菜表中的菜單編號(hào)為外鍵，他必須參照菜單表建立。這種參照關(guān)系用觸發(fā)器實(shí)現(xiàn)方法如下：Createtrigger觸發(fā)器名 on 點(diǎn)菜 afterinsertasifexists(select * from inserted where 菜品編號(hào) in (select 菜品編號(hào) from菜單)print添加成功！elsebeginprint 菜品編號(hào)與現(xiàn)有的菜單不符！rollback trabscationend4.使用觸發(fā)器的注意事項(xiàng) 一個(gè)表的每個(gè)

10、觸發(fā)操作可以有多個(gè)觸發(fā)器，可以使用系統(tǒng)存儲(chǔ)過(guò)程來(lái)指定哪個(gè)觸發(fā)器最先或最后被激活，其他觸發(fā)器順序則不能被指定。在系統(tǒng)設(shè)計(jì)過(guò)程中要慎用觸發(fā)器，因?yàn)橛|發(fā)器在大量并發(fā)的情況下，會(huì)影響系統(tǒng)性能。從維護(hù)方面考慮，當(dāng)修改觸發(fā)器所對(duì)應(yīng)的表時(shí)，該表上相應(yīng)觸發(fā)器就會(huì)失效，維護(hù)代價(jià)相應(yīng)提高。一般來(lái)說(shuō)，只要數(shù)據(jù)變動(dòng)就必須去檢查其安全性時(shí)才需要去創(chuàng)建觸發(fā)器，但是要定義好其變動(dòng)的類型。對(duì)于不是非常重要的數(shù)據(jù)沒(méi)必要?jiǎng)?chuàng)建觸發(fā)器。如我們對(duì)觸發(fā)器過(guò)分的依賴，就會(huì)影響數(shù)據(jù)庫(kù)的結(jié)構(gòu)，同時(shí)增加了維護(hù)的代價(jià)。5.總結(jié)有效利用SQL SERVER觸發(fā)器可以使系統(tǒng)具有更好的性能,并可簡(jiǎn)化序代碼。觸發(fā)器設(shè)計(jì)的好,會(huì)對(duì)數(shù)據(jù)庫(kù)系統(tǒng)的數(shù)據(jù)安全、完整性提供更好的保護(hù)，有助于系統(tǒng)的有效運(yùn)行。參