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

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

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

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

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

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

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

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

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

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

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