觸發(fā)器的操作與.pptVIP

第9章 觸發(fā)器的操作與管理,觸發(fā)器概述,觸發(fā)器是一種特殊的存儲(chǔ)過程，它在執(zhí)行語言事件時(shí)自動(dòng)生效。 SQL Server2005 包括兩大類觸發(fā)器：DML 觸發(fā)器和 DDL 觸發(fā)器。 （1）DML 觸發(fā)器在數(shù)據(jù)庫中發(fā)生數(shù)據(jù)操作語言 (DML) 事件時(shí)將啟用。DML 事件包括在指定表或視圖中修改數(shù)據(jù)的 INSERT 語句、UPDATE 語句或 DELETE 語句。DML 觸發(fā)器可以查詢其他表，還可以包含復(fù)雜的 Transact-SQL 語句。將觸發(fā)器和觸發(fā)它的語句作為可在觸發(fā)器內(nèi)回滾的單個(gè)事務(wù)對待。如果檢測到錯(cuò)誤（例如，磁盤空間不足），則整個(gè)事務(wù)即自動(dòng)回滾。 （2）DDL 觸發(fā)器是 SQL Server 2005 的新增功能。當(dāng)服務(wù)器或數(shù)據(jù)庫中發(fā)生數(shù)據(jù)定義語言 (DDL) 事件時(shí)將調(diào)用這些觸發(fā)器。,9.1 DML觸發(fā)器的創(chuàng)建和應(yīng)用,當(dāng)數(shù)據(jù)庫中發(fā)生數(shù)據(jù)操作語言 (DML) 事件時(shí)將調(diào)用 DML 觸發(fā)器。從而確保對數(shù)據(jù)的處理必須符合由這些SQL語句所定義的規(guī)則。 DML 觸發(fā)器的主要優(yōu)點(diǎn)如下： （1）DML 觸發(fā)器可通過數(shù)據(jù)庫中的相關(guān)表實(shí)現(xiàn)級(jí)聯(lián)更改。例如，可以在 titles 表的 title_id 列上寫入一個(gè)刪除觸發(fā)器，以使其他表中的各匹配行采取刪除操作。該觸發(fā)器用 title_id 列作為惟一鍵，在 titleauthor、sales 及 roysched 表中對各匹配行進(jìn)行定位。 （2）DML 觸發(fā)器可以防止惡意或錯(cuò)誤的 INSERT、UPDATE 以及 DELETE 操作，并強(qiáng)制執(zhí)行比 CHECK 約束定義的限制更為復(fù)雜的其他限制。與 CHECK 約束不同，DML 觸發(fā)器可以引用其他表中的列。 （3）DML 觸發(fā)器可以評(píng)估數(shù)據(jù)修改前后表的狀態(tài)，并根據(jù)該差異采取措施。,9.1.1 DML觸發(fā)器創(chuàng)建,當(dāng)創(chuàng)建一個(gè)觸發(fā)器時(shí)必須指定如下選項(xiàng)： （1）名稱； （2）在其上定義觸發(fā)器的表； （3）觸發(fā)器將何時(shí)激發(fā)； （4）激活觸發(fā)器的數(shù)據(jù)修改語句，有效選項(xiàng)為 INSERT、UPDATE 或 DELETE，多個(gè)數(shù)據(jù)修改語句可激活同一個(gè)觸發(fā)器； （5）執(zhí)行觸發(fā)操作的編程語句。,9.1.1 DML觸發(fā)器創(chuàng)建,DML 觸發(fā)器使用 deleted 和 inserted 邏輯表。它們在結(jié)構(gòu)上和觸發(fā)器所在的表的結(jié)構(gòu)相同，SQL Server會(huì)自動(dòng)創(chuàng)建和管理這些表?？梢允褂眠@兩個(gè)臨時(shí)的駐留內(nèi)存的表測試某些數(shù)據(jù)修改的效果及設(shè)置觸發(fā)器操作的條件。 Deleted表用于存儲(chǔ)delete，update語句所影響的行的副本。在執(zhí)行delete或update語句時(shí)，行從觸發(fā)器表中刪除，并傳輸?shù)絛eleted表中。 Inserted表用于存儲(chǔ)Insert或update語句所影響的行的副本，在一個(gè)插入或更新事務(wù)處理中，新建的行被同時(shí)添加到Inserted表和觸發(fā)器表中。Inserted表中的行是觸發(fā)器表中新行的副本。,9.1.1 DML觸發(fā)器創(chuàng)建,使用SQL Server管理平臺(tái)創(chuàng)建觸發(fā)器的過程如下： 在SQL Server管理平臺(tái)中，展開指定的服務(wù)器和數(shù)據(jù)庫項(xiàng)，然后展開表，選擇并展開要在其上創(chuàng)建觸發(fā)器的表，如圖9-1所示，右擊觸發(fā)器選項(xiàng)，從彈出的快捷菜單中選擇“新建觸發(fā)器”選項(xiàng)，則會(huì)出現(xiàn)觸發(fā)器創(chuàng)建窗口，如圖9-2所示。最后，單擊“執(zhí)行”按鈕，即可成功創(chuàng)建觸發(fā)器。,圖9-1 新建觸發(fā)器對話框,圖9-2 新建觸發(fā)器窗口,9.1.1 DML觸發(fā)器創(chuàng)建,使用CREATE TRIGGER命令創(chuàng)建DML觸發(fā)器的語法形式如下： CREATE TRIGGER schema_name.trigger_name ON table|view WITH ENCRYPTION EXECUTE AS Clause,.n FOR|AFTER|INSTEAD OF INSERT , UPDATE , DELETE WITH APPEND NOT FOR REPLICATION AS sql_statement ; .n|EXTERNAL NAME := assembly_name.class_name.method_name,9.1.1 DML觸發(fā)器創(chuàng)建,例9-1 示例說明inserted，deleted表的作用。執(zhí)行結(jié)果如右圖。 程序清單如下： create table sc (sno char(10), cno char(2), score real) Go CREATE TRIGGER tr1 ON sc FOR INSERT, UPDATE, DELETE AS PRINT inserted表： Select * from inserted PRINT deleted表： Select * from deleted Go,圖9-3 觸發(fā)器的執(zhí)行結(jié)果,9.1.1 DML觸發(fā)器創(chuàng)建,例9-2 創(chuàng)建一個(gè)觸發(fā)器，在 s 表上創(chuàng)建一個(gè)插入、更新類型的觸發(fā)器。 程序清單如下： CREATE TRIGGER tr_s ON s FOR INSERT, UPDATE AS Begin DECLARE bh varchar(6) SELECT bh =inserted.sno FROM inserted /*獲取插入或更新操作時(shí)的新值（學(xué)號(hào)）*/ End,9.1.2 DML觸發(fā)器的應(yīng)用,1. 使用INSERT觸發(fā)器 INSERT觸發(fā)器通常被用來更新時(shí)間標(biāo)記字段，或者驗(yàn)證被觸發(fā)器監(jiān)控的字段中數(shù)據(jù)滿足要求的標(biāo)準(zhǔn)，以確保數(shù)據(jù)的完整性。 例9-3建立一個(gè)觸發(fā)器，當(dāng)向sc表中添加數(shù)據(jù)時(shí)，如果添加的數(shù)據(jù)與s表中的數(shù)據(jù)不匹配（沒有對應(yīng)的學(xué)號(hào)），則將此數(shù)據(jù)刪除。 程序清單如下： CREATE TRIGGER sc_ins ON sc FOR INSERT AS BEGIN DECLARE bh char(5) Select bh=Inserted.sno from Inserted If not exists(select sno from s where s.sno=bh) Delete sc where sno=bh END,9.1.2 DML觸發(fā)器的應(yīng)用,1. 使用INSERT觸發(fā)器 例9-4創(chuàng)建一個(gè)觸發(fā)器，當(dāng)插入或更新成績列時(shí)，該觸發(fā)器檢查插入的數(shù)據(jù)是否處于設(shè)定的范圍內(nèi)。 程序清單如下： CREATE TRIGGER sc_insupd ON sc FOR INSERT, UPDATE AS DECLARE cj int, SELECT cj=inserted.score from inserted IF （cj 100） BEGIN RAISERROR （成績的取值必須在0到100之間, 16, 1） ROLLBACK TRANSACTION END,9.1.2 DML觸發(fā)器的應(yīng)用,2. 使用UPDATE觸發(fā)器 當(dāng)在一個(gè)有UPDATE觸發(fā)器的表中修改記錄時(shí)，表中原來的記錄被移動(dòng)到刪除表中，修改過的記錄插入到了插入表中，觸發(fā)器可以參考刪除表和插入表以及被修改的表，以確定如何完成數(shù)據(jù)庫操作。 例9-5 創(chuàng)建一個(gè)修改觸發(fā)器，該觸發(fā)器防止用戶修改表s的入學(xué)成績。 程序清單如下： create trigger tri_s_upd on s for update as if update（escore） begin raiserror（不能修改入學(xué)成績,16,10） rollback transaction end go,9.1.2 DML觸發(fā)器的應(yīng)用,使用UPDATE觸發(fā) 例9-6 DAS數(shù)據(jù)庫由存放實(shí)時(shí)數(shù)據(jù)的數(shù)據(jù)表以及存放歷史數(shù)據(jù)的歷史表組成。由于存放實(shí)時(shí)數(shù)據(jù)的數(shù)據(jù)表不斷更新，為了保存更新過的數(shù)據(jù)，在實(shí)時(shí)表和歷史表之間建立了觸發(fā)器。程序清單如下： CREATE TRIGGER DasD_UTRIGGER ON DasD FOR UPDATE AS BEGIN IF Update(TV) /*數(shù)據(jù)更新*/ BEGIN UPDATE DasD SET UT=getdate() /*更新時(shí)間*/ FROM DasD,inserted WHERE DasD.ID=inserted.ID INSERT DasDHis(ID,TV,UT) SELECT inserted.ID,inserted.TV,DasD.UT FROM DasD,inserted WHERE DasD.ID=inserted.ID /*將更新過的數(shù)據(jù)送入歷史庫*/ END END,9.1.2 DML觸發(fā)器的應(yīng)用,3. 使用DELETE觸發(fā)器 DELETE觸發(fā)器通常用于兩種情況，第一種情況是為了防止那些確實(shí)需要?jiǎng)h除但會(huì)引起數(shù)據(jù)一致性問題的記錄的刪除，第二種情況是執(zhí)行可刪除主記錄的子記錄的級(jí)聯(lián)刪除操作。 例9-8 建立一個(gè)與s表結(jié)構(gòu)一樣的表s1，當(dāng)刪除表s中的記錄時(shí)，自動(dòng)將刪除掉的記錄存放到s1表中。 程序清單如下： CREATE TRIGGER tr_del ON s /*建立觸發(fā)器 FOR DELETE /*對表刪除操作 AS insert s1 select * from deleted /*將刪除掉的數(shù)據(jù)送入表s1中*/ GO,9.1.2 DML觸發(fā)器的應(yīng)用,3. 使用DELETE觸發(fā)器 例9-9當(dāng)刪除表s中的記錄時(shí)，自動(dòng)刪除表sc中對應(yīng)學(xué)號(hào)的記錄。 程序清單如下： CREATE TRIGGER tr_del_s ON s FOR DELETE BEGIN DECLARE bh char(5) Select bh=deleted.sno from deleted Delete sc where sno=bh END,9.2 DDL觸發(fā)器的創(chuàng)建和應(yīng)用,DDL 觸發(fā)器會(huì)為響應(yīng)多種數(shù)據(jù)定義語言 (DDL) 語句而激發(fā)。這些語句主要是以 CREATE、ALTER 和 DROP 開頭的語句。DDL 觸發(fā)器可用于管理任務(wù)，例如審核和控制數(shù)據(jù)庫操作。 DDL 觸發(fā)器一般用于以下目的： （1）防止對數(shù)據(jù)庫架構(gòu)進(jìn)行某些更改； （2）希望數(shù)據(jù)庫中發(fā)生某種情況以響應(yīng)數(shù)據(jù)庫架構(gòu)中的更改； （3）要記錄數(shù)據(jù)庫架構(gòu)中的更改或事件。 僅在運(yùn)行觸發(fā) DDL 觸發(fā)器的 DDL 語句后，DDL 觸發(fā)器才會(huì)激發(fā)。DDL 觸發(fā)器無法作為 INSTEAD OF 觸發(fā)器使用。,9.2.1 創(chuàng)建DDL觸發(fā)器,使用CREATE TRIGGER命令創(chuàng)建DDL觸發(fā)器的語法形式如下： CREATE TRIGGER trigger_name ON ALL SERVER|DATABASEWITH ,.n FOR|AFTER event_type|event_group,.n AS sql_statement; .n|EXTERNAL NAME ; 其中： :=ENCRYPTION EXECUTE AS Clause := assembly_name.class_name.method_name,9.2.2 DDL觸發(fā)器的應(yīng)用,在響應(yīng)當(dāng)前數(shù)據(jù)庫或服務(wù)器中處理的 Transact-SQL 事件時(shí)，可以激發(fā) DDL 觸發(fā)器。觸發(fā)器的作用域取決于事件。 例9-11 使用 DDL 觸發(fā)器來防止數(shù)據(jù)庫中的任一表被修改或刪除。 程序清單如下： CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT You must disable Trigger “safety“ to drop or alter tables! ROLLBACK,9.2.2 DDL觸發(fā)器的應(yīng)用,例9-12 使用 DDL 觸發(fā)器來防止在數(shù)據(jù)庫中創(chuàng)建表。 程序清單如下： CREATE TRIGGER safety ON DATABASE FOR CREATE_TABLE AS PRINT CREATE TABLE Issued. SELECT EVENTDATA().value(/EVENT_INSTANCE/TSQLCommand/CommandText)1,nvarchar(max) RAISERROR (New tables cannot be created in this database., 16, 1) ROLLBACK,9.3 DDL查看、修改和刪除觸發(fā)器,9.3.1 查看觸發(fā)器 9.3.2 修改觸發(fā)器 9.3.3 刪除觸發(fā)器,9.3.1 查看觸發(fā)器,如果要顯示作用于表上的觸發(fā)器究竟對表有哪些操作，必須查看觸發(fā)器信息。在SQL Server中，有多種方法可以查看觸發(fā)器信息，其中最常用的有如下兩種： （1）使用SQL Server管理平臺(tái)查看觸發(fā)器信息； （2）使用系統(tǒng)存儲(chǔ)過程查看觸發(fā)器。,9.3.1 查看觸發(fā)器,（1）使用SQL Server管理平臺(tái)查看觸發(fā)器信息。 在SQL Server管理平臺(tái)中，展開服務(wù)器和數(shù)據(jù)庫，選擇并展開表，然后展開觸發(fā)器選項(xiàng)，右擊需要查看的觸發(fā)器名稱，如圖9-4所示，從彈出的快捷菜單中，選擇“編寫觸發(fā)器腳本為create到新查詢編輯器窗口”，則可以看到觸發(fā)器的源代碼。,圖9-4 查看觸發(fā)器,9.3.1 查看觸發(fā)器,（2）使用系統(tǒng)存儲(chǔ)過程查看觸發(fā)器。 系統(tǒng)存儲(chǔ)過程sp_help、sp_helptext和sp_depends分別提供有關(guān)觸發(fā)器的不同信息。其具體用途和語法形式如下。 sp_help：用于查看觸發(fā)器的一般信息，如觸發(fā)器的名稱、屬性、類型和創(chuàng)建時(shí)間。 sp_help 觸發(fā)器名稱 sp_helptext：用于查看觸發(fā)器的正文信息。 sp_helptext 觸發(fā)器名稱 sp_depends：用于查看指定觸發(fā)器所引用的表或者指定的表涉及到的所有觸發(fā)器。 sp_depends 觸發(fā)器名稱 sp_depends 表名,9.3.2 修改觸發(fā)器,通過SQL Server管理平臺(tái)、存儲(chǔ)過程，可以修改觸發(fā)器的正文和名稱。 1使用SQL Server管理平臺(tái)修改觸發(fā)器正文。 在管理平臺(tái)中，展開指定的表，右擊要修改的觸發(fā)器，從彈出的快捷菜單中選擇“修改”選項(xiàng)，則會(huì)出現(xiàn)觸發(fā)器修改窗口，如圖9-5所示。在文本框中修改觸發(fā)器的SQL語句，單擊“語法檢查”按鈕，可以檢查語法是否正確，單擊“執(zhí)行”按鈕，可以成功修改此觸發(fā)器。,圖9-5 觸發(fā)器修改窗口,9.3.2 修改觸發(fā)器,修改DML觸發(fā)器的語法形式如下： ALTER TRIGGER schema_name.trigger_name ON (table|view) WITH ,.n (FOR|AFTER|INSTEAD OF) DELETE,INSERT,UPDATE NOT FOR REPLICATION AS sql_statement;.n|EXTERNAL NAME ; :=ENCRYPTION :=ENCRY