1、觸發器觸發器 2022年3月23日星期三1在前面介紹了編寫批處理的方法，但要讓這些批處理保持永久有效，就要利用到存儲過程和觸發器了。存儲過程的存在獨立于表，觸發器的利用則與表緊密結合。存儲過程的存在獨立于表，觸發器的利用則與表緊密結合。用戶可以使用存儲過程來完善應用程序，使應用程序更有效用戶可以使用存儲過程來完善應用程序，使應用程序更有效率；可以利用觸發器來實現復雜的業務規則，更有效地實施數率；可以利用觸發器來實現復雜的業務規則，更有效地實施數據完整性。據完整性。觸發器觸發器 2022年3月23日星期三2第第1313章章 觸發器觸發器 q 概述概述 q 管理觸發器管理觸發器 q 觸發器的工作原

2、理觸發器的工作原理q 小結小結q 練習練習觸發器觸發器 2022年3月23日星期三3q 典型的應用就是銀行的取款機系統 為什么需要觸發器為什么需要觸發器帳戶信息表bank 交易信息表transInfo 張三取錢200 問題：沒有自動修改張三的余額張三開戶1000元,李四開戶1元 T-SQL語句：觸發器問題描述（銀行取款）觸發器觸發器 2022年3月23日星期三4最優的解決方案就是采用觸發器觸發器： 它是一種特殊的存儲過程 即：觸發器是在對表進行插入、更新或刪除操作時自動執行的存儲過程。 也具備事務的功能 它能在多表之間執行特殊的業務規則即：觸發器通常用于強制業務規則。 觸發器是一種高級約束，可

3、以定義比用CHECK 約束更為復雜的約束 即：觸發器可執行復雜的SQL語句（if/while/case）；可引用其它表中的列。觸發器觸發器 2022年3月23日星期三5張三張三李四李四王五王五趙二趙二王三王三宋二宋二劉五劉五插入什么是觸發器什么是觸發器刪除觸發器觸發趙二退休 趙二趙二員工表員工表退休員工表退休員工表觸發器觸發器 2022年3月23日星期三6概概 述述Microsoft SQL Server 2000 觸發器是一類特殊的存儲過程，被定義為在對表或視圖發出 UPDATE、INSERT 或 DELETE 語句時自動執行。觸發器是功能強大的工具，使每個站點可以在有數據修改時自動強制執行

4、其業務規則。觸發器可以擴展 SQL Server 約束、默認值和規則的完整性檢查邏輯，但只要約束和默認值提供了全部所需的功能，就應使用約束和默認值。當有操作影響到觸發器保護的數據時，觸發器就自動發生。因此，觸發器是在特定表上進行定義的，該表也稱為觸發器表。當有操作針對觸發器表時，例如在表中插入、刪除、更新數據時，如果該表有相應操作類型的觸發器，那么觸發器就自動觸發執行。因為觸發器在操作有效之后才能執行，所以在修改中他們代表“最后的動作”。假如導致觸發器的一個請求失敗，SQL Server系統將拒絕更新數據，并且對那些應用程序返回一個錯誤消息。表可以有多個觸發器。CREATE TRIGGER 語

5、句可以與 FOR UPDATE、FOR INSERT 或 FOR DELETE 子句一起使用，指定觸發器專門用于特定類型的數據修改操作。當指定 FOR UPDATE 時，可以使用 IF UPDATE (column_name) 子句，指定觸發器專門用于具體某列的更新。觸發器觸發器 2022年3月23日星期三7觸發器定義在特定的表上，與表相關自動觸發執行不能直接調用是一個事務（可回滾）觸發器觸發器 2022年3月23日星期三8【存儲過程】存儲過程是一組 Transact-SQL 語句，在一次編譯后可以執行多次。因為不必重新編譯 Transact-SQL 語句，所以執行存儲過程可以提高性能。【觸發

6、器】觸發器是一種特殊類型的存儲過程，不由用戶直接調用。創建觸發器時會對其進行定義，以便在對特定表或列作特定類型的數據修改時執行。CREATE PROCEDURE 或 CREATE TRIGGER 語句不能跨越批處理。即存儲過程或觸發器始終只能在一個批處理中創建并編譯到一個執行計劃中。觸發器既然是一種特殊類型的存儲過程，那么它與存儲過程也有一些區別。例如，存儲過程可以由用戶直接調用執行，但是觸發器不能被直接調用執行。觸發器觸發器 2022年3月23日星期三9在SQL Server 中一張表可以有多個觸發器。用戶可以針對INSERT、UPDATE或DELETE語句分別設置觸發器，也可以針對一張表上

7、的特定操作設置多個觸發器。觸發器里可以容納非常復雜的Transact-SQL 語句。但是不但是不管觸發器所進行的操作有多復雜，觸發器都只作為一個獨立的單元被執行，管觸發器所進行的操作有多復雜，觸發器都只作為一個獨立的單元被執行，被看做是一個事務。如果在執行觸發器的過程中發生了錯誤，則整個事務將被看做是一個事務。如果在執行觸發器的過程中發生了錯誤，則整個事務將會自動回滾。會自動回滾。觸發器在服務器將特定的操作（觸發器在服務器將特定的操作（INSERT、UPDATE或或DELETE ）執行）執行結束后才執行。結束后才執行。觸發器觸發器 2022年3月23日星期三10使用觸發器的最終目的是更好地維護

8、企業的業務規則。在實際運用中，觸發器主要提供以下功能： 級聯修改數據庫中的所有相關表。如：publishers表中的內容被刪除后，將自動對titles、titleauthor等表的修改。 撤銷或回滾違反引用完整性的操作，防止非法修改數據。 執行比檢查約束CHECK更復雜的約束操作。 查找在數據修改前后，表狀態之間的差別，并根據差別來分別采取相應的措施。 在一張表同一類型的操作（INSERT、UPDATE或DELETE ）上設置多個觸發器，從而可以針對同樣的修改語句執行不同的多種操作。觸發器觸發器 2022年3月23日星期三11創建觸發器創建觸發器創建觸發器，觸發器是一種特殊的存儲過程，在用戶試

9、圖對指定的表執行指定的數據修改語句時自動執行。Microsoft SQL Server 允許為任何給定的 INSERT、UPDATE 或 DELETE 語句創建多個觸發器。觸發器的類型：DELETE 觸發器INSERT 觸發器UPDATE 觸發器觸發器觸發器 2022年3月23日星期三12創建觸發器時需指定： 名稱。在其上定義觸發器的表。觸發器將何時激發。激活觸發器的數據修改語句。有效選項為 INSERT、UPDATE 或 DELETE。多個數據修改語句可激活同一個觸發器。執行觸發操作的編程語句。 觸發器觸發器 2022年3月23日星期三13創建觸發器前應考慮下列問題： CREATE TRIG

10、GER 語句必須是批處理中的第一個語句。將該批處理中隨后的其它所有語句解釋為 CREATE TRIGGER 語句定義的一部分。創建觸發器的權限默認分配給表的所有者，且不能將該權限轉給其他用戶。觸發器為數據庫對象，其名稱必須遵循標識符的命名規則。雖然觸發器可以引用當前數據庫以外的對象，但只能在當前數據庫中創建觸發器。雖然不能在臨時表或系統表上創建觸發器，但是觸發器可以引用臨時表。不應引用系統表，而應使用信息架構視圖。在含有用 DELETE 或 UPDATE 操作定義的外鍵的表中，不能定義 INSTEAD OF 和 INSTEAD OF UPDATE 觸發器。雖然 TRUNCATE TABLE 語

11、句類似于沒有 WHERE 子句（用于刪除行）的 DELETE 語句，但它并不會引發 DELETE 觸發器，因為 TRUNCATE TABLE 語句沒有記錄。WRITETEXT 語句不會引發 INSERT 或 UPDATE 觸發器。 觸發器觸發器 2022年3月23日星期三141使用T-SQL語句創建觸發器創建觸發器使用CREATE TRIGGER語句。CREATE TRIGGER 權限默認授予定義觸發器的表所有者、sysadmin 固定服務器角色成員以及 db_owner 和 db_ddladmin 固定數據庫角色成員，并且不可轉讓。觸發器觸發器 2022年3月23日星期三15【語法格式語法格

12、式】CREATE TRIGGER trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , UPDATE WITH APPEND NOT FOR REPLICATION AS IF UPDATE ( column ) AND | OR UPDATE ( column ) .n | IF ( COLUMNS_UPDATED ( ) bitwise_operator updated_bitmask ) comparison_operator column_bitmask .n sql_stateme

13、nt .n 觸發器觸發器 2022年3月23日星期三16q創建觸發器的語法：CREATE TRIGGER trigger_name ON table_name WITH ENCRYPTION FOR DELETE, INSERT, UPDATE AS T-SQL語句GO WITH ENCRYPTION表示加密觸發器定義的SQL文本 DELETE, INSERT, UPDATE指定觸發器的類型觸發器觸發器 2022年3月23日星期三17【示例】 使用帶有提醒消息的觸發器當有人試圖在 titles 表中添加或更改數據時，下例將向客戶端顯示一條消息。USE pubsIF EXISTS (SELECT

14、 name FROM sysobjects WHERE name = reminder AND type = TR) DROP TRIGGER reminderGOCREATE TRIGGER reminderON titlesFOR INSERT, UPDATE AS RAISERROR (50009, 16, 10)GO觸發器觸發器 2022年3月23日星期三18【示例】使用帶有提醒電子郵件的觸發器當 titles 表更改時，下例將電子郵件發送給指定的人員 (MaryM)。USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name =

15、 reminder AND type = TR) DROP TRIGGER reminderGOCREATE TRIGGER reminderON titlesFOR INSERT, UPDATE, DELETE AS EXEC master.xp_sendmail MaryM, Dont forget to print a report for the distributors.GO觸發器觸發器 2022年3月23日星期三19【示例】在 employee 和 jobs 表之間使用觸發器業務規則由于 CHECK 約束只能引用定義了列級或表級約束的列，表間的任何約束（在下例中是指業務規則）都必須

16、定義為觸發器。下例創建一個觸發器，當插入或更新雇員工作級別 (job_lvls) 時，該觸發器檢查指定雇員的工作級別（由此決定薪水）是否處于為該工作定義的范圍內。若要獲得適當的范圍，必須引用 jobs 表。USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = employee_insupd AND type = TR) DROP TRIGGER employee_insupdGO觸發器觸發器 2022年3月23日星期三20CREATE TRIGGER employee_insupdON employeeFOR INSERT, U

17、PDATEAS/* Get the range of level for this job type from the jobs table. */DECLARE min_lvl tinyint, max_lvl tinyint, emp_lvl tinyint, job_id smallintSELECT min_lvl = min_lvl, max_lvl = max_lvl, emp_lvl = i.job_lvl, job_id = i.job_idFROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id JOIN job

18、s j ON j.job_id = i.job_id觸發器觸發器 2022年3月23日星期三21IF (job_id = 1) and (emp_lvl 10) BEGIN RAISERROR (Job id 1 expects the default level of 10., 16, 1) ROLLBACK TRANSACTION ENDELSE IF NOT (emp_lvl BETWEEN min_lvl AND max_lvl) BEGIN RAISERROR (The level for job_id:%d should be between %d and %d., 16, 1,

19、job_id, min_lvl, max_lvl) ROLLBACK TRANSACTION END觸發器觸發器 2022年3月23日星期三22【例】創建一個針對products 表執行update操作時被觸發的觸發器，返回共修改了多少行數據。USE NorthwindIF EXISTS (SELECT name FROM sysobjects WHERE name=tr_product_update AND type=TR)DROP TRIGGER tr_product_updateGOUSE NorthwindGOCREATE TRIGGER tr_product_update ON pr

20、oductsFOR update AS DECLARE msg varchar(100) SELECT msg=str(rowcount)+employees updated by this statement PRINT msg RETURNGO觸發器觸發器 2022年3月23日星期三23在創建觸發器的語句中，禁止使用下列T-SQL語句：ALTER DATABASE、ALTER PROCEDURE、ALTER TABLE、ALTER TRIGGER 、ALTER VIEWCREATE DATABASE、CREATE DEFAULT、CREATE INDEX、 CREATE PROCEDURE

21、、 CREATE RULE、CREATE TABLE、 CREATE TRIGGERDROP DATABASE、 DROP DEFAULT、DROP INDEX、DROP PROCEDURE、DROP RULE、 DROP TABLE、DROP TRIGGER、DROP VIEW GRANT RESTORE DATABASE、RESTORE LOG REVOKETRUNCATE TABLE觸發器觸發器 2022年3月23日星期三24注：在CREATE TRIGGER語句中不能使用SELECT語句返回對表格查詢的數據，因為觸發器不接受用戶應用程序傳遞的參數，從而也無法向用戶應用程序返回查詢表格數

22、據所得到的結果。由于系統表具有存儲數據的特殊性和重要性，所以建議用戶不要自己在系統表上建立觸發器。在創建觸發器時，不允許RETURN返回體現運行狀態的數據。觸發器觸發器 2022年3月23日星期三25 【例】 創建一個當products表執行INSERT、UPDATE操作時觸發的觸發器。 create trigger tr_products_insupd on products for insert,update as 由于SQL Server支持在同一個表的同一種操作類型上建立多個觸發器，所以當建立完tr_products_insupd觸發器后， 在products表執行INSERT操作時，

23、將觸發tr_products_insupd觸發器，在執行update操作時，將觸發tr_product_update和tr_products_insupd觸發器，他們都是有效的觸發器。觸發器觸發器 2022年3月23日星期三26 2使用企業管理器來創建觸發器可以使用Enterprise Manager 來創建觸發器，步驟是：（1）選中要創建觸發器的表所在的數據庫，展開該節點。（2）展開該數據庫節點的表節點。（3）選中指定的表，右擊鼠標，從彈出的快捷菜單中展開“所有任務”子菜單，選擇“管理觸發器”。（4）彈出對話框。（5）在“文本”文本框中輸入觸發器的語句。（6）單擊“檢查語法”按鈕，檢查語法錯

24、誤。（7）單擊“確定”按鈕完成觸發器的創建。觸發器觸發器 2022年3月23日星期三27修改觸發器修改觸發器修改觸發器使用ALTER TIGGER語句。語法格式為：ALTER TIGGER trigger_nameON TABLE WITH ENCRYPTIONFOR DELETE , UPDATE , INSERT NOT FOR REPLICATION AS SQL_statement .n用戶也可以用Enterprise Manager 來修改觸發器，步驟與創建觸發器的步驟類似。觸發器觸發器 2022年3月23日星期三28嵌套觸發器嵌套觸發器如果一個觸發器在執行操作時引發了另一個觸發器，

25、而這個觸發器又接著引發下一個觸發器.，這些觸發器就是嵌套觸發器。觸發器可嵌套至32層，并且可以控制是否可以通過“嵌套觸發器”服務器配置選項進行觸發器嵌套。觸發器觸發器 2022年3月23日星期三29查看觸發器查看觸發器 觸發器是特殊的存儲過程，適用于存儲過程的管理方式都適用于觸發器，所以用戶完全可以使用sp_helptText、sp_help、sp_depends等系統存儲過程以及使用企業管理器來瀏覽觸發器的有關信息。也可以使用sp_rename 系統存儲過程來為觸發器更名。(1)使用sp_helptrigger系統存儲過程來顯示指定表上指定類型的觸發器的信息【語法格式】 sp_helptri

26、gger table_name,type其中，type是觸發器類型的取值范圍，包括INSERT、UPDATE和DELETE。如果不設置type的值，則返回定義在該表上所有觸發器的信息。【例】 sp_helptrigger products,update 觸發器觸發器 2022年3月23日星期三30(2) 使用sp_helptext系統存儲過程顯示一個指定觸發器的代碼。【例】 sp_helptext tr_products_insupd與存儲過程一樣，在定義或修改觸發器的語句中使用WITH ENCRYPTION子句，觸發器的代碼也可以被加密。被加密的觸發器的代碼將不能使用sp_helptext系

27、統存儲過程來查看。(3) 使用sp_depends系統存儲過程來顯示一個表上觸發器的依賴關系。【例】 sp_depends tr_products_insupd觸發器觸發器 2022年3月23日星期三31刪除觸發器刪除觸發器1使用T-SQL語句刪除觸發器刪除觸發器使用DROP TRIGGER語句。 【語法格式】DROP TRIGGER 觸發器名,n用戶刪除某個表格時，所有建立在該表上的觸發器都將被刪除。2使用企業管理器刪除觸發器觸發器觸發器 2022年3月23日星期三32觸發器的工作原理觸發器的工作原理Microsoft SQL Server 2000系統提供了3種類型的觸發器，即INSERT

28、類型、UPDATE類型和DELETE類型。當向表中插入數據時，INSERT觸發器觸發執行。當INSERT觸發器觸發時，新的記錄增加到觸發器表中和inserted表中。該inserted表是一個邏輯表，保存了所插入記錄的拷貝，允許用戶參考INSERT語句中數據。觸發器可以檢查inserted表，來確定該觸發器的操作是否應該執行和如何執行。在inserted表中的那些記錄，總是觸發器表中一行或多行記錄的冗余。當觸發一個DELETE觸發器時，被刪除的記錄放在一個特殊的deleted表中。deleted表是一個邏輯表，用來保存已經從表中刪除的記錄。該deleted表允許參考原來的DELETE語句刪除的

29、已經記錄在日志中的數據。修改一條記錄就等于插入一條新記錄和刪除一條舊記錄。同樣，UPDATE語句也可以看成是由刪除一條記錄的DELETE語句和增加一條記錄的INSERT語句組成。當在某一個有UPDATE觸發器表的上面修改一條記錄時，表中原來的記錄移動到deleted表中，修改過的記錄插入到了inserted表中。觸發器可以檢查deleted表和inserted表以及被修改的表，以便確定是否修改了多個行和應該如何執行觸發器的操作。觸發器觸發器 2022年3月23日星期三33執行觸發器時，系統創建了兩個特殊的邏輯表：inserted表表和deleted表表。它們由系統來維護，用戶不能對其進行修改。

30、它們存在于內存中而不是數據庫中。這兩個表的結構總是與被觸發器作用的表的結構相同。觸發器執行完成后，與該觸發器相關的這兩個表也會被刪除。inserted邏輯表：存放由于執行INSERT或UPDATE語句而要從表中插入的所有行。在執行INSERT或UPDATE操作時，新的行同時添加到激活觸發器的表中和inserted表中，inserted表的內容是激活觸發器的表中新行的拷貝。deleted邏輯表：存放由于執行DELETE或UPDATE語句而要從表中刪除的所有行。在執行DELETE或UPDATE操作時，被刪除的行從激活觸發器的表中被移動到deleted表中，這兩個表（DELETE 表和觸發器表不會有

31、共同的行。觸發器中使用的特殊表觸發器中使用的特殊表觸發器觸發器 2022年3月23日星期三34對具有觸發器的表（觸發器表）進行操作時，其操作過程為：執行INSERT操作插入到觸發器表中的新行被插入到inserted表中。執行DELETE操作從觸發器表中刪除的行被插入到deleted表中。執行UPDATE操作先從觸發器表中刪除舊行，然后再插入新行。其中，被刪除的舊行被插入到deleted表中，插入的新行被插入到inserted表中。deleted表和inserted表都是針對當前觸發器的局部臨時表，這些表只對應于當前觸發器的基本表。如果在觸發器中使用了存儲過程，或是產生了嵌套觸發器的情況，則不同的觸發器將會使用屬于自己基本表的deleted和inserted臨時表。觸發器觸發器 2022年3月23日星期三35q 觸發器觸發時q系統自動在內存中創建deleted表或inserted表q只讀，不允許修改；觸發器執行完成后，自動刪除q inserted 表 q臨時保存了插入或更新后的記錄行 q可以從inserted表中檢查插入的數據是否滿足業務需求q 如果不滿足，則向用戶報告錯誤消息，并回滾插入操作q deleted 表q臨時保存了刪除或更新前的記錄行 q可以從deleted表中檢查被刪除的數據是否滿足業務