數(shù)據(jù)庫原理與應用：第10章 觸發(fā)器的創(chuàng)建和使用

1、數(shù)據(jù)庫原理與應用數(shù)據(jù)庫原理與應用第十講第十講 觸發(fā)器創(chuàng)建和使用觸發(fā)器創(chuàng)建和使用1、創(chuàng)建觸發(fā)器、創(chuàng)建觸發(fā)器2 、查看、修改和刪除觸發(fā)器、查看、修改和刪除觸發(fā)器3 、觸發(fā)器的應用、觸發(fā)器的應用 本講主要內(nèi)容觸發(fā)器觸發(fā)器是一種特殊類型的存儲過程，它不同于前觸發(fā)器是一種特殊類型的存儲過程，它不同于前面介紹過的存儲過程。觸發(fā)器主要是通過事件進行觸面介紹過的存儲過程。觸發(fā)器主要是通過事件進行觸發(fā)而被執(zhí)行的，而存儲過程可以通過存儲過程名稱而發(fā)而被執(zhí)行的，而存儲過程可以通過存儲過程名稱而被直接調(diào)用。觸發(fā)器是一個功能強大的工具，它使每被直接調(diào)用。觸發(fā)器是一個功能強大的工具，它使每個站點可以在有數(shù)據(jù)修改時自動強制

2、執(zhí)行其業(yè)務規(guī)則。個站點可以在有數(shù)據(jù)修改時自動強制執(zhí)行其業(yè)務規(guī)則。觸發(fā)器可以用于觸發(fā)器可以用于 SQL Server 約束、默認值和規(guī)則的約束、默認值和規(guī)則的完整性檢查。完整性檢查。SQL SERVER可以檢查數(shù)據(jù)完整性的還有什么情況可以檢查數(shù)據(jù)完整性的還有什么情況?比如前面講過的比如前面講過的CHECK約束。但是，與約束。但是，與CHECK約束相比，約束相比，觸發(fā)器可以強制實現(xiàn)更加復雜的數(shù)據(jù)完整性，而且可以參考其他觸發(fā)器可以強制實現(xiàn)更加復雜的數(shù)據(jù)完整性，而且可以參考其他表的字段。表的字段。注意注意:觸發(fā)器可以擴展觸發(fā)器可以擴展SQL Server約束、默認值和規(guī)則的約束、默認值和規(guī)則的完整性檢

3、查邏輯，但只要約束和默認值提供了全部所需的功能，完整性檢查邏輯，但只要約束和默認值提供了全部所需的功能，就應使用約束和默認值。就應使用約束和默認值。觸發(fā)器可以使用觸發(fā)器可以使用T一一SQL語句進行復雜的邏輯處理，語句進行復雜的邏輯處理，它基于一個表創(chuàng)建，但是可以對多個表進行操作，因此常它基于一個表創(chuàng)建，但是可以對多個表進行操作，因此常常用于復雜的業(yè)務規(guī)則。工般可以使用觸發(fā)器完成如下功常用于復雜的業(yè)務規(guī)則。工般可以使用觸發(fā)器完成如下功能能觸發(fā)器的作用1、級聯(lián)修改數(shù)據(jù)庫中相關的表。、級聯(lián)修改數(shù)據(jù)庫中相關的表。例如例如:在數(shù)據(jù)庫在數(shù)據(jù)庫company中有兩個表中有兩個表project和和custom

4、er,表表project中包含項目信息和項目的合作客戶名，而中包含項目信息和項目的合作客戶名，而customer表中則表中則存儲所有客戶的信息。如果刪除了存儲所有客戶的信息。如果刪除了customer表中的某一客戶，如表中的某一客戶，如張三，那么在張三，那么在project表中所有和該客戶有關的項目記錄都應該做表中所有和該客戶有關的項目記錄都應該做相應的調(diào)整。如在相應的調(diào)整。如在project表中將所有合作客戶為張三的字段改為表中將所有合作客戶為張三的字段改為null或者其它值，或者刪除整條和張三有關的記錄。而使用觸發(fā)器或者其它值，或者刪除整條和張三有關的記錄。而使用觸發(fā)器就可以實現(xiàn)這樣的功能

5、。就可以實現(xiàn)這樣的功能。2、執(zhí)行比核查約束更為復雜的約束操作。、執(zhí)行比核查約束更為復雜的約束操作。 在觸發(fā)器中可以書寫更復雜的在觸發(fā)器中可以書寫更復雜的T一一SQL語句，例如可以引語句，例如可以引用多個表，并使用用多個表，并使用if . else等語句做更復雜的檢查。等語句做更復雜的檢查。3、拒絕或回滾違反引用完整性的操作。、拒絕或回滾違反引用完整性的操作。 檢查對數(shù)據(jù)表的操作是否違反引用完整性，并選擇相應的操檢查對數(shù)據(jù)表的操作是否違反引用完整性，并選擇相應的操作。作。4、比較表修改前后數(shù)據(jù)之間的差別，并根據(jù)差別采取相應、比較表修改前后數(shù)據(jù)之間的差別，并根據(jù)差別采取相應的操作。例如的操作。例如

6、:若想規(guī)定每次工資的變動幅度不能超過若想規(guī)定每次工資的變動幅度不能超過40%，使用，使用觸發(fā)器可以將修改后的表數(shù)據(jù)和修改前的表數(shù)據(jù)進行比較，若超觸發(fā)器可以將修改后的表數(shù)據(jù)和修改前的表數(shù)據(jù)進行比較，若超出出40%，可以回滾該修改操作。，可以回滾該修改操作。 觸發(fā)器的類型觸發(fā)器可以分為五種類型觸發(fā)器可以分為五種類型:Update觸發(fā)器觸發(fā)器:在表上進行更新操作時觸發(fā)。在表上進行更新操作時觸發(fā)。Insert觸發(fā)器觸發(fā)器:在表上進行更新操作時觸發(fā)在表上進行更新操作時觸發(fā)Delete觸發(fā)器觸發(fā)器:在表上進行更新操作時觸發(fā)在表上進行更新操作時觸發(fā)Instead of觸發(fā)器觸發(fā)器:不執(zhí)行插入、更新或刪除操作

7、時，將觸發(fā)不執(zhí)行插入、更新或刪除操作時，將觸發(fā)Instead of觸發(fā)器。觸發(fā)器。After觸發(fā)器觸發(fā)器:在一個觸發(fā)動作發(fā)生之后激發(fā)，并提供一種機制以便在一個觸發(fā)動作發(fā)生之后激發(fā)，并提供一種機制以便控制多個觸發(fā)器的執(zhí)行順序?？刂贫鄠€觸發(fā)器的執(zhí)行順序。AFTER要求只有執(zhí)行某一操作要求只有執(zhí)行某一操作(INSERT,UPDATE、DELETE)之后觸之后觸發(fā)器才被觸發(fā)，且只能在表上定義?？梢詾獒槍Ρ淼耐徊僮鞫x多個觸發(fā)器才被觸發(fā)，且只能在表上定義?？梢詾獒槍Ρ淼耐徊僮鞫x多個觸發(fā)器。對于發(fā)器。對于AFTER觸發(fā)器，可以定義哪一個觸發(fā)器被最先觸發(fā)，哪一個被觸發(fā)器，可以定義哪一個觸發(fā)器被最先觸發(fā)

8、，哪一個被最后觸發(fā)，通常使用系統(tǒng)過程最后觸發(fā)，通常使用系統(tǒng)過程sp settriggerorder來完成此任務。來完成此任務。INSTEAD OF觸發(fā)器表示并不執(zhí)行其所定義的操作觸發(fā)器表示并不執(zhí)行其所定義的操作(INSERT、UPDATE、DELETE)，而僅是執(zhí)行觸發(fā)器本身。既，而僅是執(zhí)行觸發(fā)器本身。既可在表上定義可在表上定義INSTEAD OF觸發(fā)器，也可以在視圖上定義觸發(fā)器，也可以在視圖上定義INSTEAD OF觸發(fā)器，但對同一操作只能定義一個觸發(fā)器，但對同一操作只能定義一個INSTEAD OF觸發(fā)器。觸發(fā)器。 觸發(fā)器主要優(yōu)點如下：觸發(fā)器是自動的：當對表中的數(shù)據(jù)作了任何修改（比如手觸發(fā)器

9、是自動的：當對表中的數(shù)據(jù)作了任何修改（比如手工輸入或者應用程序采取的操作）之后立即被激活。工輸入或者應用程序采取的操作）之后立即被激活。 觸發(fā)器可以通過數(shù)據(jù)庫中的相關表進行層疊更改。觸發(fā)器可以通過數(shù)據(jù)庫中的相關表進行層疊更改。 觸發(fā)器可以強制限制，這些限制比用觸發(fā)器可以強制限制，這些限制比用 CHECK 約束所定義約束所定義的更復雜。的更復雜。 創(chuàng)建觸發(fā)器 創(chuàng)建觸發(fā)器應該考慮以下幾個問題：創(chuàng)建觸發(fā)器應該考慮以下幾個問題：CREATE TRIGGER 語句必須是批處理中的第一個語句。語句必須是批處理中的第一個語句。創(chuàng)建觸發(fā)器的權限默認分配給表的所有者，且不能將該權限轉(zhuǎn)給其創(chuàng)建觸發(fā)器的權限默認分配

10、給表的所有者，且不能將該權限轉(zhuǎn)給其他用戶。他用戶。 觸發(fā)器為數(shù)據(jù)庫對象，其名稱必須遵循標識符的命名規(guī)則。觸發(fā)器為數(shù)據(jù)庫對象，其名稱必須遵循標識符的命名規(guī)則。 雖然觸發(fā)器可以引用當前數(shù)據(jù)庫以外的對象，但只能在當前數(shù)據(jù)庫雖然觸發(fā)器可以引用當前數(shù)據(jù)庫以外的對象，但只能在當前數(shù)據(jù)庫中創(chuàng)建觸發(fā)器。中創(chuàng)建觸發(fā)器。 雖然不能在臨時表或系統(tǒng)表上創(chuàng)建觸發(fā)器，但是觸發(fā)器可以引用臨雖然不能在臨時表或系統(tǒng)表上創(chuàng)建觸發(fā)器，但是觸發(fā)器可以引用臨時表。時表。 在含有用在含有用 DELETE 或或 UPDATE 操作定義的外鍵的表中，不能定義操作定義的外鍵的表中，不能定義 INSTEAD OF 和和 INSTEAD OF U

11、PDATE 觸發(fā)器。觸發(fā)器。雖然雖然 TRUNCATE TABLE 語句類似于沒有語句類似于沒有 WHERE 子句（用于刪子句（用于刪除行）的除行）的 DELETE 語句，但它并不會引發(fā)語句，但它并不會引發(fā) DELETE 觸發(fā)器，因為觸發(fā)器，因為 TRUNCATE TABLE 語句沒有記錄。語句沒有記錄。WRITETEXT 語句不會引發(fā)語句不會引發(fā) INSERT 或或 UPDATE 觸發(fā)器。觸發(fā)器。 當創(chuàng)建一個觸發(fā)器時必須指定：當創(chuàng)建一個觸發(fā)器時必須指定： 名稱；在其上定義觸發(fā)器的表；名稱；在其上定義觸發(fā)器的表；觸發(fā)器將何時激發(fā)；激活觸發(fā)器的數(shù)據(jù)修改語句。觸發(fā)器將何時激發(fā)；激活觸發(fā)器的數(shù)據(jù)修改

12、語句。 下面的語句不可以用于創(chuàng)建觸發(fā)器下面的語句不可以用于創(chuàng)建觸發(fā)器:Alter Database、Create Database、Disk Init、Disk Resize、Drop Database、Load Database、Load Log、Reconfigure、RestoreDatabase、Restore Log.當創(chuàng)建一個觸發(fā)器時必須指定當創(chuàng)建一個觸發(fā)器時必須指定:(1)名稱名稱;(2)在其上定義觸發(fā)在其上定義觸發(fā)器的表器的表;(3)觸發(fā)器將何時激發(fā)觸發(fā)器將何時激發(fā);(4)激活觸發(fā)器的數(shù)據(jù)修改語句。激活觸發(fā)器的數(shù)據(jù)修改語句。1. 使用企業(yè)管理器創(chuàng)建觸發(fā)器在企業(yè)管理器中，展開指定

13、的服務器和數(shù)據(jù)庫項，然后展在企業(yè)管理器中，展開指定的服務器和數(shù)據(jù)庫項，然后展開要在其上創(chuàng)建觸發(fā)器的表所在的數(shù)據(jù)庫，用右鍵單擊該表，從開要在其上創(chuàng)建觸發(fā)器的表所在的數(shù)據(jù)庫，用右鍵單擊該表，從彈出的快捷菜單中選擇所有任務子菜單下的管理觸發(fā)器選項，則彈出的快捷菜單中選擇所有任務子菜單下的管理觸發(fā)器選項，則會出現(xiàn)觸發(fā)器屬性對話框。在該對話框中，在名稱文本框中選擇會出現(xiàn)觸發(fā)器屬性對話框。在該對話框中，在名稱文本框中選擇新建，然后在文本框中輸入觸發(fā)器文本。單擊新建，然后在文本框中輸入觸發(fā)器文本。單擊“檢查語法檢查語法”按鈕，按鈕，則可以檢查語法是否正確。單擊則可以檢查語法是否正確。單擊“應用應用”按鈕，則

14、在名稱下拉列按鈕，則在名稱下拉列表中會有新創(chuàng)建的觸發(fā)器名稱。最后，單擊表中會有新創(chuàng)建的觸發(fā)器名稱。最后，單擊“確定確定”按鈕，即可按鈕，即可關閉該對話框，成功創(chuàng)建觸發(fā)器。關閉該對話框，成功創(chuàng)建觸發(fā)器。2. 使用CREATE TRIGGER命令創(chuàng)建觸發(fā)器其語法形式如下：其語法形式如下：CREATE TRIGGER trigger_name ONtable|view WITHENCRYPTION FOR | AFTER | INSTEAD OF DELETE, INSERT , UPDATE WITHAPPEND NOTFORREPLICATION AS IFUPDATE(column) AND|

15、ORUPDATE(column) .n |IF(COLUMNS_UPDATED()bitwise_operatorupdated_bitmask) comparison_operatorcolumn_bitmask.n sql_statement.n 各參數(shù)的說明如下:Trigger_ame:是用戶要創(chuàng)建的觸發(fā)器的名字。觸發(fā)器的名字必須符合是用戶要創(chuàng)建的觸發(fā)器的名字。觸發(fā)器的名字必須符合MS SQL Server的命名規(guī)則，且其名字在當前數(shù)據(jù)庫中必須是惟一的的命名規(guī)則，且其名字在當前數(shù)據(jù)庫中必須是惟一的.Table:是與用戶創(chuàng)建的觸發(fā)器相關聯(lián)的表的名字，并且該表己經(jīng)存在。是與用戶創(chuàng)建的觸發(fā)器相

16、關聯(lián)的表的名字，并且該表己經(jīng)存在。WITH ENCRYPTION:表示對包含有表示對包含有CREATE TRIGGER文本的文本的syscomments表進行加密表進行加密DELETE，INSERT，UPDATE:關鍵字用來指明哪種數(shù)據(jù)操作將關鍵字用來指明哪種數(shù)據(jù)操作將激活觸發(fā)器，至少要指明一個選項，在觸發(fā)器的定義中三者的順序不受限制，激活觸發(fā)器，至少要指明一個選項，在觸發(fā)器的定義中三者的順序不受限制，且各選項要用逗號隔開。且各選項要用逗號隔開。AFTER:表示只有在執(zhí)行了指定的操作表示只有在執(zhí)行了指定的操作INSERT, DELETE, UPDATE之后觸發(fā)器才被激活，執(zhí)行觸發(fā)器中的之后觸發(fā)

17、器才被激活，執(zhí)行觸發(fā)器中的SQL語句。若使用關鍵語句。若使用關鍵字字FOR，則表示為，則表示為AFTER觸發(fā)器，且該類型觸發(fā)器僅能在表上創(chuàng)建。觸發(fā)器，且該類型觸發(fā)器僅能在表上創(chuàng)建。INSTEAD OF:當為表或視圖定義了針對某一操作當為表或視圖定義了針對某一操作INSERT、DELETE、UPDATE的的INSTEAD OF類型觸發(fā)器，且執(zhí)行了相應的操作類型觸發(fā)器，且執(zhí)行了相應的操作時，盡管觸發(fā)器被觸發(fā)，但相應的操作并不被執(zhí)行，而運行的僅是觸發(fā)時，盡管觸發(fā)器被觸發(fā)，但相應的操作并不被執(zhí)行，而運行的僅是觸發(fā)器器SQL語句本身。語句本身。例子例子Use company Go-創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器C

18、reate trigger 觸發(fā)器觸發(fā)器-歡迎新員工歡迎新員工On employee After insertAs Print 新員工加入本公司，歡迎歡迎新員工加入本公司，歡迎歡迎! go例： 創(chuàng)建了一個觸發(fā)器，在創(chuàng)建了一個觸發(fā)器，在 titles 表上創(chuàng)建一個插入、更新類型的表上創(chuàng)建一個插入、更新類型的觸發(fā)器，其程序清單如下：觸發(fā)器，其程序清單如下：USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = reminder AND type = TR) DROP TRIGGER reminderGOCREATE TRIGGER r

19、eminderON titlesFOR INSERT, UPDATE AS sql_statementsGO查看、修改和刪除觸發(fā)器查看、修改和刪除觸發(fā)器1. 查看觸發(fā)器查看觸發(fā)器（）使用企業(yè)管理器查看觸發(fā)器信息（）使用企業(yè)管理器查看觸發(fā)器信息（）使用系統(tǒng)存儲過程查看觸發(fā)器（）使用系統(tǒng)存儲過程查看觸發(fā)器sp_help、sp_helptext和sp_depends具體用途和語法形式如下。 sp_help：用于查看觸發(fā)器的一般信息，如觸發(fā)：用于查看觸發(fā)器的一般信息，如觸發(fā)器的名稱、屬性、類型和創(chuàng)建時間。器的名稱、屬性、類型和創(chuàng)建時間。sp_help 觸發(fā)器名稱觸發(fā)器名稱 sp_helptext：用于

20、查看觸發(fā)器的正文信息：用于查看觸發(fā)器的正文信息sp_helptext 觸發(fā)器名稱觸發(fā)器名稱 sp_depends：用于查看指定觸發(fā)器所引用的表：用于查看指定觸發(fā)器所引用的表或者指定的表涉及到的所有觸發(fā)器?；蛘咧付ǖ谋砩婕暗降乃杏|發(fā)器。sp_depends 觸發(fā)器名稱觸發(fā)器名稱 sp_depends 表名表名 修改觸發(fā)器 （）使用企業(yè)管理器修改觸發(fā)器正文（）使用企業(yè)管理器修改觸發(fā)器正文在企業(yè)管理器中，展開指定的服務器和數(shù)在企業(yè)管理器中，展開指定的服務器和數(shù)據(jù)庫，選擇指定的數(shù)據(jù)庫和表，用右鍵單擊要修據(jù)庫，選擇指定的數(shù)據(jù)庫和表，用右鍵單擊要修改的表，從彈出的快捷菜單中選擇所有任務子菜改的表，從彈出

21、的快捷菜單中選擇所有任務子菜單下的管理觸發(fā)器選項，則會出現(xiàn)觸發(fā)器屬性對單下的管理觸發(fā)器選項，則會出現(xiàn)觸發(fā)器屬性對話框。在名稱選項框中選擇要修改的觸發(fā)器的名話框。在名稱選項框中選擇要修改的觸發(fā)器的名稱，然后在文本框中修改觸發(fā)器的稱，然后在文本框中修改觸發(fā)器的SQL語句，單語句，單擊擊“檢查語法檢查語法”按鈕，可以檢查語法是否正確。按鈕，可以檢查語法是否正確。 （）使用sp_rename命令修改觸發(fā)器的名稱 sp_rename命令的語法形式如下：命令的語法形式如下：sp_rename oldname,newname（）使用alter trigger命令修改觸發(fā)器正文alter trigger命令的

22、語法形式如下：命令的語法形式如下：ALTERTRIGGERtrigger_name ON(table|view) WITHENCRYPTION ( FOR | AFTER | INSTEAD OF ) DELETE , INSERT , UPDATE NOTFORREPLICATION AS sql_statement.n | (FOR|AFTER|INSTEADOF)INSERT,UPDATE NOTFORREPLICATION AS IFUPDATE(column) AND|ORUPDATE(column) .n | IF ( COLUMNS_UPDATED ( ) bitwise_ope

23、rator updated_bitmask ) comparison_operatorcolumn_bitmask.n sql_statement.n 刪除觸發(fā)器（）使用系統(tǒng)命令（）使用系統(tǒng)命令DROP TRIGGER刪除指定的觸刪除指定的觸發(fā)器，其語法形式如下：發(fā)器，其語法形式如下：DROP TRIGGER trigger ,.n （）刪除觸發(fā)器所在的表時，（）刪除觸發(fā)器所在的表時，SQL Server將會自動將會自動刪除與該表相關的觸發(fā)器。刪除與該表相關的觸發(fā)器。（）在企業(yè)管理器中，用右鍵單擊要刪除的觸發(fā)器（）在企業(yè)管理器中，用右鍵單擊要刪除的觸發(fā)器所在的表，從彈出的快捷菜單中選擇所有任務

24、子菜單下的所在的表，從彈出的快捷菜單中選擇所有任務子菜單下的管理觸發(fā)器選項，則會出現(xiàn)觸發(fā)器屬性對話框。在名稱選管理觸發(fā)器選項，則會出現(xiàn)觸發(fā)器屬性對話框。在名稱選項框中選擇要刪除的觸發(fā)器，單擊項框中選擇要刪除的觸發(fā)器，單擊“刪除刪除”按鈕，即可刪按鈕，即可刪除該觸發(fā)器。除該觸發(fā)器。觸發(fā)器的應用 1. 使用使用INSERT觸發(fā)器觸發(fā)器INSERT觸發(fā)器通常被用來更新時間標記字段，或者觸發(fā)器通常被用來更新時間標記字段，或者驗證被觸發(fā)器監(jiān)控的字段中的數(shù)據(jù)滿足要求的標準，以驗證被觸發(fā)器監(jiān)控的字段中的數(shù)據(jù)滿足要求的標準，以確保數(shù)據(jù)完整性。確保數(shù)據(jù)完整性。2. 使用使用UPDATE觸發(fā)器觸發(fā)器修改觸發(fā)器和插

25、入觸發(fā)器的工作過程基本上一致，修改觸發(fā)器和插入觸發(fā)器的工作過程基本上一致，修改一條記錄等于插入了一條新的記錄并且刪除一條舊修改一條記錄等于插入了一條新的記錄并且刪除一條舊的記錄。的記錄。3.使用DELETE觸發(fā)器DELETE觸發(fā)器通常用于兩種情況，第一種情況觸發(fā)器通常用于兩種情況，第一種情況是為了防止那些確實需要刪除但會引起數(shù)據(jù)一致是為了防止那些確實需要刪除但會引起數(shù)據(jù)一致性問題的記錄的刪除。性問題的記錄的刪除。第二種情況是執(zhí)行可刪除主記錄的子記錄的級聯(lián)第二種情況是執(zhí)行可刪除主記錄的子記錄的級聯(lián)刪除操作?？梢允褂眠@樣的觸發(fā)器從主銷售記錄刪除操作?？梢允褂眠@樣的觸發(fā)器從主銷售記錄中刪除所有的定單項。中刪除所有的定單項。4. 使用嵌套的觸發(fā)器 如果一個觸發(fā)器在執(zhí)行操作時引發(fā)了另一個觸發(fā)如果一個觸發(fā)器在執(zhí)行操作時引發(fā)了另一個觸發(fā)器，而這個觸發(fā)器又接著引發(fā)下一個觸發(fā)器器，而這個觸發(fā)器又接著引發(fā)下一個觸發(fā)器這些觸發(fā)器就是嵌套觸發(fā)器。觸發(fā)器可嵌套至這些觸發(fā)器就是嵌套觸發(fā)器。觸發(fā)器可嵌套至 32 層，并且可以控制是否可以通過層，并且可以控制是否可以通過嵌套觸發(fā)器嵌套觸發(fā)器服務器配置選項進行觸發(fā)器嵌套。如果允許使服務器配置選項進行觸發(fā)器嵌套。如果允許使用嵌套觸發(fā)器，且鏈中的一個觸發(fā)器開始一個無用嵌套觸發(fā)器，且鏈中的一個觸發(fā)器開始一