第十一章 觸發(fā)器

1、第十章 觸發(fā)器(Trigger)觸發(fā)器是數(shù)據(jù)庫服務(wù)器中發(fā)生事件時(shí)能自動執(zhí)行的一種特種存儲過程，主要用于強(qiáng)制規(guī)則和數(shù)據(jù)完整性。1、觸發(fā)器概述11觸發(fā)器的概念觸發(fā)器是一種特殊類型的存儲過程，主要通過事件進(jìn)行觸發(fā)而被執(zhí)行的。由DML觸發(fā)的是DML觸發(fā)器，由DDL觸發(fā)的是DDL觸發(fā)器（DDL觸發(fā)器是SQL Server 2005中新增的，分為數(shù)據(jù)庫級的DDL觸發(fā)器和服務(wù)器級的DDL觸發(fā)器）。這里主要介紹DML觸發(fā)器。DML觸發(fā)器是和數(shù)據(jù)庫中的表相關(guān)的，當(dāng)對表進(jìn)行INSERT、UPDATE、DELETE操作時(shí)，將觸發(fā)相應(yīng)的觸發(fā)器。在SQL Server中，有兩類觸發(fā)器，一類是標(biāo)準(zhǔn)（After）觸發(fā)器，另

2、一類是新的Instead-of觸發(fā)器。12觸發(fā)器的功能l 強(qiáng)化約束。觸發(fā)器能夠?qū)崿F(xiàn)比CHECK更為復(fù)雜的約束。l 級聯(lián)修改。當(dāng)某張表中的數(shù)據(jù)發(fā)生變化時(shí)，通過觸發(fā)器對與之關(guān)聯(lián)的表進(jìn)行相應(yīng)的更新。l 實(shí)施數(shù)據(jù)完整性。使用觸發(fā)器禁止或回滾違反數(shù)據(jù)完整性的操作或其它不經(jīng)許可的操作。l 更新前后狀態(tài)比較。觸發(fā)器可以區(qū)對數(shù)據(jù)更新操作的前后狀態(tài)差別，并可以完成基于這種差別的特定動作。13說明（DML觸發(fā)器）l 觸發(fā)原因。每個(gè)修改語句（Insert、Update、Delete）都會引發(fā)一次觸發(fā)器的執(zhí)行。甚至當(dāng)修改語句影響了0條記錄時(shí)也會被引發(fā)。l 觸發(fā)時(shí)機(jī)。After觸發(fā)器是在修改語句成功完成之后被引發(fā)，而I

3、nstead-of觸發(fā)器是在修改語句完成之前（Inserted和Deleted表被建立之后），在所有約束之前被啟動l 視圖觸發(fā)器。對視圖只能建立Instead-of觸發(fā)器，不能建立After觸發(fā)器。l 一個(gè)表的一個(gè)操作能定義多個(gè)AFTER觸發(fā)器，根據(jù)定義的先后順序被觸發(fā)，也可以通過sp_settriggerorder來改變觸發(fā)順序。一個(gè)表或一個(gè)視圖對于每種操作只能定義一個(gè)Instead-of觸發(fā)器。l2、觸發(fā)器的創(chuàng)建在SQL Server中創(chuàng)建觸發(fā)器有兩種方法：使用企業(yè)管理器創(chuàng)建和使用CREATE TRIGGER語句創(chuàng)建。1 使用企業(yè)管理器創(chuàng)建創(chuàng)建觸發(fā)器步驟（演示）2使用CREATE TRIG

4、GER語句創(chuàng)建觸發(fā)器（一）創(chuàng)建觸發(fā)器的簡化語法CREATE TRIGGER trigger_nameON table_name | viewFOR | AFTER | INSTEAD OFINSERT,UPDATE,DELETEASsql_statementn（二）格式說明（1）trigger_name：觸發(fā)器名稱。每個(gè) trigger_name 必須遵循標(biāo)識符規(guī)則，但 trigger_name 不能以 # 或 # 開頭。（2）table_name | view：對其執(zhí)行 DML 觸發(fā)器的表或視圖，有時(shí)稱為觸發(fā)器表或觸發(fā)器視圖。視圖只能被 INSTEAD OF 觸發(fā)器引用。不能對局部或全局臨時(shí)

5、表定義 DML 觸發(fā)器。（3）FOR | AFTER：指定 DML 觸發(fā)器僅在觸發(fā) SQL 語句中指定的所有操作都已成功執(zhí)行時(shí)才被激發(fā)。所有的引用級聯(lián)操作和約束檢查也必須在激發(fā)此觸發(fā)器之前成功完成。不能對視圖定義 AFTER 觸發(fā)器。一個(gè)操作能定義多個(gè)AFTER觸發(fā)器，根據(jù)定義的先后順序被觸發(fā)。（4）INSTEAD OF：表示不執(zhí)行其所定義的操作（INSERT、UPDATE、DELETE）而僅執(zhí)行觸發(fā)器本身。這種觸發(fā)器可以在表和視圖上定義。但對于同一操作只能定義一個(gè)INSTEAD OF觸發(fā)器。INSTEAD OF觸發(fā)器在所有約束之前被啟動，其優(yōu)先級高于觸發(fā)語句的操作，這個(gè)行為和AFTER觸發(fā)器

6、有很大不同。如果在INSTEAD OF觸發(fā)器內(nèi)部啟動了修改語句（INSERT、UPDATE、DELETE），修改語句不會再次啟動觸發(fā)器。一個(gè)操作只能有一個(gè)INSTEAD OF觸發(fā)器。（5）INSERT,UPDATE,DELETE：用來指明哪種數(shù)據(jù)操作將激活觸發(fā)器，允許使用上述選項(xiàng)的任意順序組合，順序不受限制。（三）例例1：在employee表上創(chuàng)建一個(gè)delete觸發(fā)器，顯示被刪除員工的個(gè)數(shù)信息CREATE TRIGGER tr_employee ON employeeAFTER DELETEASDECLARE msg varchar(50)SELECT msg=STR(ROWCOUNT)+

7、were deletedSELECT msgRETURN例2：在employee表上創(chuàng)建一個(gè)instead of 觸發(fā)器，當(dāng)插入一條記錄時(shí)，先判斷l(xiāng)astname上的值是否存在，如果不存在則插入，否則不插入。create trigger insert_employee on employeesinstead of insertasif not exists(select * from employees,inserted where inserted.lastname = employees.lastname)insert into employees(employeeid,lastname,

8、firstname) select employeeid,lastname,firstname from insertedset IDENTITY_INSERT dbo.employees oninsert into employees(employeeid,lastname,firstname)values(11,'bb','bbbb')3、inserted和deleted虛擬表在觸發(fā)器的執(zhí)行過程中，SQL Server要維護(hù)兩個(gè)臨時(shí)的虛擬表：inserted和deleted。Inserted表包含引發(fā)觸發(fā)器操作過程中被插入的所有記錄，deleted表包含引發(fā)

9、觸發(fā)器操作過程中被刪除的所有記錄（update操作可理解為先刪除，后插入）。我們可以利用這一特性來測試某些數(shù)據(jù)修改的效果，以及設(shè)置觸發(fā)操作的條件。觸發(fā)器被激活時(shí)，兩個(gè)表的內(nèi)容如下： Transact-SQL語句Inserted表Deleted表 INSERT新添加的行空（N/A） UPDATE新行舊行 DELETE空被刪除的行例：為customers表創(chuàng)建一個(gè)名為test_tr的觸發(fā)器，當(dāng)執(zhí)行添加、更新或刪除時(shí)，激活該觸發(fā)器。create trigger test_tron customersfor insert,update,deleteasselect * from insertedsel

10、ect * from deleted4、通過觸發(fā)器處理多記錄上的改動SQL語句是集合操作，Insert、Update、Delete也同樣。有時(shí)需要通過觸發(fā)器一條記錄一條記錄的處理更改（這里不包括Delete），函數(shù)update()和columns_updated()非常有用。update()函數(shù)：對于Update操作，確定在update操作過程中指定列是否已被更更，是則返回True，對于Delete操作，對所有列的測試均返回True。columns_update()函數(shù)：測試是否插入或更新了提及的列，僅用于 INSERT 或 UPDATE 觸發(fā)器中。COLUMNS_UPDATED 返回 var

11、binary 位模式，表示插入或更新了表中的哪些列。COLUMNS_UPDATED 函數(shù)以從左到右的順序返回位，最左邊的位表示表中的第一列（參見CREATE TRIGGER）。例：為products表建立一個(gè)名為muti_updated觸發(fā)器，當(dāng)修改單價(jià)大于100的產(chǎn)品的單價(jià)時(shí)，自動記錄修改信息。create table updatedinfo(productid int primary key,oldunitprice money,newunitprice money,updatetime datetime)goCREATE TRIGGER muti_updated ON dbo.Products FOR UPDATEASif update(unitprice)insert into updatedinfo(productid,oldunitprice,newunitprice,updatetime) select ductid,d.unitprice,i.unitprice,getdate() from deleted d inner join inserted i on ductid=ductidgoupdate pr