數(shù)據(jù)庫設(shè)計（7/9）：觸發(fā)器

youxd 2016-07-11

展開全文

對于設(shè)計和創(chuàng)建數(shù)據(jù)庫完全是個新手？沒關(guān)系，Joe Celko，世界上讀者數(shù)量最多的SQL作者之一，會告訴你這些基礎(chǔ)。和往常一樣，即使是最專業(yè)的數(shù)據(jù)庫老手，也會給他們帶來驚喜。Joe是DMBS雜志是多年來最受讀者喜愛的作者。他在美國、英國，北歐，南美及非洲傳授SQL知識。他在ANSI / ISO SQL標(biāo)準(zhǔn)委員會工作了10年，為SQL-89和SQL-92標(biāo)準(zhǔn)做出了杰出貢獻。

在第1篇到第4篇，我們創(chuàng)建了表，架構(gòu)的基礎(chǔ)和可視化。第5篇和第6篇講了存儲過程。這篇文章會講述在觸發(fā)器上，你需要盡量避免的特性。

通常你會被告知，觸發(fā)器是存儲過程的“特殊類別”，但這不完全對。它沒有參數(shù)，你不能觸發(fā)它，它有其它地方不存在本地偽表（local pseudo-tables）。在我們詳細(xì)討論前，我們停下來，討論下在基礎(chǔ)表里，修改數(shù)據(jù)的SQL模型。

INSERT,UPDATE和DELETE

SQL是面向集合語言，因此它用數(shù)據(jù)集合同時修改表。它不是面向記錄語言，它會一次修改一條記錄來處理數(shù)據(jù)，按順序來（想下磁帶和打孔卡）。修改表內(nèi)容的3個基本操作是INSERT，UPDATE和DELETE。

插入是它們最容易理解的。這是概念上的模型，不是實際的實現(xiàn)。你把行集合放入現(xiàn)存表。但比這更多。這個集合一次作為整個單元，因此用CURRENT_TIMESTAMP這樣常量的任何函數(shù)調(diào)用，對于所有的新行有同樣的值。標(biāo)識（IDENTITY）表性質(zhì)（性質(zhì)，不是列），當(dāng)在增加表級別計數(shù)器的時候，通過查詢行和嘗試寫入違反了這個模型。IDENTITY值不確定性，取決于在插入時索引和機械裝置的物理狀態(tài)。

在ANSI/ISO標(biāo)準(zhǔn)和T-SQL的INSERT里，新行保存在偽表里，命名為新。如果插入會把表引入違反任何約束的狀態(tài)，那么事務(wù)會被系統(tǒng)回滾，你會收到錯誤信息。那就是說新行絕不會插入到表（但是，標(biāo)識屬性會增加）。

刪除是類似的。在ANSI/ISO標(biāo)準(zhǔn)和T-SQL里DELETE里，在基表里符合DELETE FROM語句的WHERE字句的所有行，放入偽表里，命名為舊。如果刪除會把表引入違法任何約束的狀態(tài)，那么事務(wù)會被系統(tǒng)回滾，你會收到錯誤信息。那就是說舊行絕不離開表。

實際上，大多數(shù)SQL引擎會在一次通過里標(biāo)記舊行，在最后通過里檢查約束并移除它們。

更新是刪除和插入的組合。概念模型是我們在WHERE子句上查找并創(chuàng)建刪除偽表，就像一個DELETE FROM。然后我們看SET子句上創(chuàng)建新的要插入的偽表。

表由行組成，行由列組成。在SET子句里每個賦值同時完成。如果一列從SET子句里丟失，SQL引擎實際上創(chuàng)建一個“SET <列名>=<列名>'--什么也不做的賦值。這不像過程化語言從左到右處理。這個語句會在列a和列b里交換值，且一次完成。

UPDATE FoobarSET a = b, b = a;

這個過程語句會把列a和列b設(shè)置為同樣的值，因為它從左到右執(zhí)行。

BEGIN SET a = b; SET b = a; END;

刪除行會被移除，插入行在表里持續(xù)，所有都一次完成。如果更新會把表引入違反任何約束的狀態(tài)，那么事務(wù)會被系統(tǒng)回滾，你會收到錯誤信息。那就是說表絕不修改。

數(shù)據(jù)庫事件

觸發(fā)器是附加到一個且只有一個表的代碼。但一個表可以有多個觸發(fā)器。有CREATE TRIGGER語句，因為它是持續(xù)架構(gòu)對象?；镜腡-SQL語法非常直接：

CREATE TRIGGER ON {FOR | AFTER | INSTEAD OF} {[INSERT] [,] [UPDATE] [,] [DELETE]}AS;

trigger name和table name或view name就是對應(yīng)的對象名稱。AFTER和FOR是等價的關(guān)鍵字，但AFTER更有描述行（其它SQL會BEFORE觸發(fā)器）。稍后我們會講解INSTEAD OF選項。

INSERT，UPDATE，DELETE被稱為數(shù)據(jù)庫事件或動作。SELECT語句，DROP和ALTER不是數(shù)據(jù)庫事件。當(dāng)在表上這些操作中的一個完成，在數(shù)據(jù)庫動作成功完成后，觸發(fā)器會被觸發(fā)。對于每個數(shù)據(jù)庫事件，觸發(fā)器主體里的代碼在表層級上一次執(zhí)行。

注意。你的程序更新表T1，它觸發(fā)觸發(fā)器TR1，它更新表T1本身。因為表T1被更新，觸發(fā)器TR1再次觸發(fā)，如此循壞。可能毫無休止。但它不需要在那里停止。你的觸發(fā)器可以引起在其它表上觸發(fā)器觸發(fā)。觸發(fā)器也可以內(nèi)嵌的。假設(shè)程序更新表T1，觸發(fā)了觸發(fā)器TR1，它更新了表T2。在T2上的觸發(fā)器觸發(fā)，再次更新表T1。這個模式可以擴展到多個表，不停止循環(huán)。

SQL引擎需要跟蹤所有這些修改，這樣的話如果有東西出錯的話，它可以進行回滾。避免寫這樣的代碼：它耗資源，運行緩慢且會鎖掉整個數(shù)據(jù)庫。它也很難維護。

觸發(fā)器主體

觸發(fā)器主體是一塊T-SQL過程化語句塊。但有一點區(qū)別。你不能穿參數(shù)給觸發(fā)器主體，像一個存儲過程。觸發(fā)器主體會訪問INSERTED和DELETED偽表。如果你想要的話，可以重命名這些偽表。

這個格式也有特殊的邏輯功能：UPDATE（<列名>）和COLUMNS_UPDATED。這些測試來看一個UPDATE FROM或INSERT INTO語句在他們的參數(shù)列表里是否修改一個或多個列。這是專有功能，因此接下來我會詳細(xì)講解，一個簡答的例子會是：

CREATE TRIGGER No_Embezzlement_TriggerON PayrollAFTER UPDATEASIF UPDATE(payroll_amt)BEGINRAISEERROR ('You cannot give yourself a raise');ROLLBACK TRANSACTION;END;

INSTEAD OF觸發(fā)器

更新視圖被熟知為一個NP完全問題。在英語里，那是說，我們知道在合理的次數(shù)里沒有常規(guī)的方式來完成它，就如問題變得越來越大。

INSTEAD OF觸發(fā)器是我們更新視圖并回避RDBMS的算法限制。一個NSTEAD OF觸發(fā)器執(zhí)行觸發(fā)器主體，不是它觸發(fā)的數(shù)據(jù)庫動作。這用例子來解釋更容易。假設(shè)我們有以在兩個表上有join和聚合函數(shù)定義的視圖：

CREATE VIEW SalesSummary (order_nbr, order_amt_tot)ASSELECT O.order_nbr, O.customer_name, SUM(D.unit_price * D.order_qty) FROM Orders AS O, Order_Details AS D WHERE O.order_nbr = D.order_nbr GROUP BY O.order_nbr;

如果對SalesSummary數(shù)據(jù)庫事件有個觸發(fā)器，不使用INSTEAD OF觸發(fā)器的話它會失敗。這個視圖有join，計算和聚合來確保它是不可更新的。但INSTEAD OF觸發(fā)器不是一個前觸發(fā)器！真正的前觸發(fā)器會執(zhí)行它的代碼，然后嘗試完成數(shù)據(jù)庫事件。INSTEAD OF觸發(fā)器會獨自執(zhí)行它的代碼并完成。

記住視圖是虛擬的：它物理且持續(xù)不存在。那就是說你沒有已刪除和已插入的偽表來使用。再進一步，你不能從INSERT INTO，DELETE FROM或UPDATE語句里拿到參數(shù)。所有代碼需要在基礎(chǔ)表上操作。

INSTEAD OF觸發(fā)器也可以和視圖一樣運行在基礎(chǔ)表上。通常它不這樣用。T-SQL習(xí)慣上進行AFTER觸發(fā)器，檢查下結(jié)果和修正，或者如果有問題的話回滾。

審計觸發(fā)器

使用觸發(fā)器的一個常見技巧是在一個或多個表里收集審計數(shù)據(jù)。通常認(rèn)為這不是個好主意。它通過增加額外的讀寫降低了應(yīng)用的性能。對于SELECT語句沒有觸發(fā)器，因此你不能跟蹤誰在查看數(shù)據(jù)。健康保險攜帶和責(zé)任法案（HIPAA (Health Insurance Portability and Accountability Act)）和許多其它法律需要對每個查看的數(shù)據(jù)都有記錄。

但不止這些，審計的基本原則是審計從被審計的事物分離。例如，當(dāng)一個采購訂單創(chuàng)建了一個貨運，負(fù)責(zé)運輸?shù)娜撕屯膺@份訂單的不是同一個人。發(fā)運到你本人的誘惑避免這個方法。

假設(shè)表上有列用戶事件日期和雇員更新記錄的用戶id，這由觸發(fā)器來完成。直到你考慮到它，這才聽起來不錯。觸發(fā)器一直在，不會被推翻。哎呀！如果你刪除了行，審計數(shù)據(jù)也一起刪掉。如果有人可以訪問審計列，它們會被更新為任何值。

第三方審計工具使用事務(wù)日志文件，它是服務(wù)器為恢復(fù)和用來捕獲所有需要審計的動作的網(wǎng)絡(luò)帶寬創(chuàng)建，來保持?jǐn)?shù)據(jù)安全，物理上從剩下的數(shù)據(jù)庫分離。這會通過法律測試。記住ROI在今天的美國里，表示”監(jiān)禁的危險（Risk of Incarceration）“，不是”投資回報率（return on investment）“。

數(shù)據(jù)和引用完整性觸發(fā)器

如果你有舊的SQL代碼遷移到新發(fā)布的SQL，你可以看下是否有觸發(fā)器可以用DRI（引用完整性）約束和動作來替換。這是觸發(fā)器的初衷。例如，在訂單表里一個訂單被刪除，觸發(fā)器會刪除訂單明細(xì)表里的所有相關(guān)記錄?？梢詥栂虑拜?，當(dāng)我們忘記觸發(fā)器或用錯它們，花了多少時間來找無關(guān)聯(lián)的行。

現(xiàn)在，這些完整性觸發(fā)器的大部分可以用聲明DRI操作來代替。它們對DELETE和UPDATE動作進行簡單的動作。這個動作是在DDL上的選項子句。完整語法是：

FOREIGN KEY () REFERENCES ()[ON UPDATE | ON DELETE][NO ACTION | CASCADE | SET NULL | SET DEFAULT]

NO ACTION：一個錯誤信息告訴用戶這個操作不允許，我們得到一個回滾。

CASCADE：在外鍵關(guān)系里刪除或更新所有涉及到行數(shù)據(jù)。

SET NULL：設(shè)置引用列為NULL。這假設(shè)對于表，所有外鍵列允許接受NULL。

SET DEFAULT:這是引用表列為定義的默認(rèn)值。這假設(shè)對于表，所有列有定義的默認(rèn)值。

只有在完整性規(guī)則復(fù)雜的時候才使用觸發(fā)器。一個我能想到的，當(dāng)一個成員插入或刪除時，在多個組涉及值的重發(fā)布的例子。即使那樣，考慮把它放入存儲過程。

T-SQL對DDL觸發(fā)器也有擴展。這些被DDL事件觸發(fā)，而不是DML事件——CREATE, ALTER, DROP, GRANT, DENY, REVOKE 或 UPDATE STATISTICS語句。

同個事件的多個觸發(fā)器

對于同個數(shù)據(jù)庫事件有不止一個觸發(fā)器是合法的。這不是個好主意，但在T-SQL里是合法的。嘗試在一個觸發(fā)器里完成，這樣的話容易維護。

默認(rèn)情況下，在SQL Server表里，對于同個操作的多個觸發(fā)器是不確定的。但是，對于兩個AFTER觸發(fā)器使用系統(tǒng)存儲過程settriggerorder聲明觸發(fā)順序還是可能的。這個存儲過程不能用在INSTEAD OF觸發(fā)器。

語法非常直接：

參數(shù)@order表示觸發(fā)起是否第一個還是最有一個觸發(fā)。如果指定NONE，那么沒有強制順序，我們回到了默認(rèn)的狀態(tài)。顯然你不能有2個FIRST或2個LAST觸發(fā)器。

但是，如果你有第3個觸發(fā)器，它不是FIRST，也不是LAST，那它肯定在觸發(fā)順序里的中間。

小結(jié)

各位，不止T-SQL，有觸發(fā)器的專有實現(xiàn)。因此它們不遷移。它們行為的一些可以是非確定性的，因此它們很難調(diào)試。它們不告訴優(yōu)化器它可以使用的任何東西，像DRI動作做的?？墒悄愫苡锌赡軙l(fā)現(xiàn)，它們是確認(rèn)復(fù)雜數(shù)據(jù)完整行規(guī)則的最安全方法。偶第神啊！