數(shù)據(jù)庫應(yīng)用基礎(chǔ)第八章觸發(fā)器

1、1第八章 觸發(fā)器復(fù)習(xí)1 如何執(zhí)行存儲(chǔ)過程？2 變量的定義方法。38.1 觸發(fā)器概述8.2 創(chuàng)建觸發(fā)器使用觸發(fā)器8.4 修改和刪除觸發(fā)器主要內(nèi)容：4觸發(fā)器的基本概念 觸發(fā)器概述 數(shù)據(jù)庫觸發(fā)器是一種在基表做UPDATE、INSERT或DELETE操作時(shí)被修自動(dòng)執(zhí)行的內(nèi)嵌過程，用來防止對(duì)數(shù)據(jù)進(jìn)行的不正確或不一致的修改 5 通過觸發(fā)器可以把事務(wù)規(guī)則從應(yīng)用程序代碼移到數(shù)據(jù)庫中從而確保事務(wù)規(guī)則被遵守，并能顯著提高性能 ，保證安全，并實(shí)現(xiàn)過程完整性。6類型：DDL(Data Definition Language)數(shù)據(jù)定義語言觸發(fā)器DML(Data Manipulation Language)數(shù)據(jù)操作語言觸

2、發(fā)器7使用觸發(fā)器的優(yōu)點(diǎn) 1. 強(qiáng)制比CHCEK約束更復(fù)雜的數(shù)據(jù)完整性 在CHECK約束中不允許引用其他表中的列來完成檢查工作，而觸發(fā)器則可以引用其他表中的列來完成數(shù)據(jù)完整性的約束,如： 進(jìn)出貨.存貨量定購(gòu)量82. 使用自定義的錯(cuò)誤信息 用戶有時(shí)需要在數(shù)據(jù)完整性遭到破壞或其他情況下，發(fā)出預(yù)先自定義好的錯(cuò)誤信息或動(dòng)態(tài)自定義的錯(cuò)誤信息93. 實(shí)現(xiàn)數(shù)據(jù)庫中多張表的級(jí)聯(lián)修改 在titleauthor、sales及roysched表中對(duì)各匹配行進(jìn)行定位刪除title_id列titles表在title_id列上定義一個(gè)刪除觸發(fā)器titleauthor表sales表roysched表此三列均有title_id

3、字段104. 比較數(shù)據(jù)庫修改前后數(shù)據(jù)的狀態(tài) 用戶可在觸發(fā)器中引用由于修改所影響的記錄行 觸發(fā)器提供訪問由INSERT、UPDATE或DELETE語句引起的數(shù)據(jù)變化的前后狀態(tài)115. 維護(hù)非規(guī)范化數(shù)據(jù) 非規(guī)范數(shù)據(jù)通常是指在表中的派生的、冗余的數(shù)據(jù)值維護(hù)非規(guī)范化數(shù)據(jù)應(yīng)該通過使用觸發(fā)器來實(shí)現(xiàn)表的級(jí)聯(lián)是指不同表之間的主外鍵關(guān)系，維護(hù)表的級(jí)聯(lián)可通過設(shè)置表的主鍵與外鍵的關(guān)系來實(shí)現(xiàn)注意區(qū)別128.2 創(chuàng)建觸發(fā)器CREATE TRIGGER owner.trigge_nameON owner.table|viewFOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETE在觸發(fā)SQL語句

4、中指定的所有操作(引用級(jí)聯(lián)操作和約束檢查)成功完成后，執(zhí)行此觸發(fā)器 表示不執(zhí)行其所定義的操作(INSERT、UPDATE、DELETE)而僅執(zhí)行觸發(fā)器本身激發(fā)觸發(fā)器的定時(shí)時(shí)間13WITH ENCRYPTIONAS IF UPDATE(column_name)and|or UPDATE(column_name) sql_statesments指定對(duì)標(biāo)內(nèi)某字段作增加或修改操作時(shí)觸發(fā)器材起作用定義觸發(fā)器被觸發(fā)后，將執(zhí)行的數(shù)據(jù)庫操作 148.2.1 INSERT觸發(fā)器 例：在pubs庫的authors表上創(chuàng)建 my_trigger1觸發(fā)器，該觸發(fā)器被操作INSERT所觸發(fā)USE pubsgoCREAT

5、E TRIGGER my_trigger1ON authorsFOR INSERTASraiserror(unauthorized,10,1)15當(dāng)向表authors插入數(shù)據(jù)時(shí)將觸發(fā)觸發(fā)器，但是數(shù)據(jù)仍能被插入表中INSERT INTO authorsvalues(172-33-1234,White,John,408496-7223,10932Bigge Rd.,Menlo Park,CA,94025, 1) 定義觸發(fā)器時(shí)指定了FOR選項(xiàng)，AFTER為默認(rèn)值，觸發(fā)器只在INSERT中指定的操作都執(zhí)行后才激發(fā)，因此仍能插入數(shù)據(jù)16Deleted表和 Inserted SQL Server為每個(gè)觸發(fā)

6、器都創(chuàng)建了兩個(gè)專用表：INSERTED表和DELETED表。這兩個(gè)邏輯表，由系統(tǒng)來維護(hù)。用戶不能對(duì)它們進(jìn)行修改。它們存放在內(nèi)存中而不是數(shù)據(jù)庫中。這兩個(gè)表的結(jié)構(gòu)總是與被該觸發(fā)器作用的表的結(jié)構(gòu)相同。觸發(fā)器執(zhí)行完成后，與該觸發(fā)器相關(guān)的這兩個(gè)表也被刪除。17DELETED表存放由于執(zhí)行DELETE或UPDATE語句而要從表中刪除的所有行。在執(zhí)行DELETE或UPDATE操作時(shí)，被刪除的行從激活觸發(fā)器的表中被移動(dòng)到DELETED表，這兩個(gè)表不會(huì)有共同的行。18INSERTED表存放由于執(zhí)行INSERT或UPDATE語句而要向表中插入的所有行。在執(zhí)行INSERT或UPDATE操作時(shí)，新的行同時(shí)添加到激活觸

7、發(fā)器的表和INSERTD表中， INSERTD表的內(nèi)容是激活觸發(fā)器的表中新行的備份。19有沒有什么辦法能實(shí)現(xiàn)觸發(fā)器被執(zhí)行的同時(shí)，取消觸發(fā)觸發(fā)器的SQL語句的操作呢？用INSTEAD OF關(guān)鍵字來實(shí)現(xiàn)20例:在pubs庫的authors表上創(chuàng)建觸發(fā)器my_trigger2，它被操作DELETE所觸發(fā)，且要求觸發(fā)觸發(fā)器的DELETE語句在執(zhí)行后被取消USE PubsGOCREATE TRIGGER my_trigger2ON authorsINSTEAD OF DELETEASraiserror(No Right To Delete It,10,1)21如果在表authors中刪除前面例子中新增的

8、記錄，就會(huì)報(bào)錯(cuò)，如：DELETE from authorsWhere au_id=172-33-1234 228.2.2 UPDATE觸發(fā)器 通過定義IF UPDATE 實(shí)現(xiàn)當(dāng)特定列被更新時(shí)觸發(fā)觸發(fā)器不論該更新影響表中的多少行 若用戶需要實(shí)現(xiàn)多個(gè)特定列中的任意一列被更新時(shí)觸發(fā)觸發(fā)器，可以通過在觸發(fā)器定義中使用多個(gè)IF UPDATE 語句來實(shí)現(xiàn) 23例:在pubs庫的authors表上建觸發(fā)器member_trigger，它將被UPDATE操作激活,且不允許修改au_lname字段USE pubsgoCREATE TRIGGER member_triggerON authorsFOR UPDAT

9、EAS24不使用INSTEAD OF而是通過rollback transaction子句恢復(fù)原來的數(shù)據(jù)的方法來實(shí)現(xiàn)字段不被修改IF UPDATE(au_lname)BEGIN raiserror(Unauthorized!,10,1) rollback transactionEND25建好觸發(fā)器后試著執(zhí)行UPDATE操作USE pubsgoUPDATE authorsSET au_lname=WangtiWHERE au_lname=White運(yùn)行結(jié)果顯示：“Unauthorized!” 26在“查詢分析器”中運(yùn)行如下命令：USE pubsgoSELECT au_lnameFROM autho

10、rsWHERE au_lname LIKE W% 查詢結(jié)果中只有兩行的White的內(nèi)容，可見剛才的更新操作并不能實(shí)現(xiàn)對(duì)表中au_lname字段的更新 27UPDATE可對(duì)未建立保護(hù)性觸發(fā)的字段進(jìn)行更新而不激發(fā)觸發(fā)器，例如：USE pubsgoUPDATE authorsSET au_fname=WangtiWHERE au_fname=Johnson檢索表authors可看到表內(nèi)的信息被更新了，這是由于在au_fname字段上未建立UPDATE的觸發(fā)返回消息：“所影響的行數(shù)為 1 行”288.2.3 DELETE 觸發(fā)器 利用DELETE觸發(fā)器，能在相應(yīng)的表中實(shí)現(xiàn)當(dāng)遇到刪除動(dòng)作時(shí)自動(dòng)發(fā)出報(bào)警

11、29例：在pubs庫的sales表上建立名為delete_trigger的DELETE觸發(fā)器，實(shí)現(xiàn)對(duì)刪除sales表中銷售記錄的操作給出報(bào)警，并取消當(dāng)前的刪除操作USE pubsgoCREATE TRIGGER delete_trigger ON salesFOR DELETEAS BEGIN raiserror(Unauthorized!,10,1) rollback transactionEND 308.2.4 查看觸發(fā)器信息 象存儲(chǔ)過程一樣，觸發(fā)器在創(chuàng)建后，系統(tǒng)把觸發(fā)器的名稱保存在系統(tǒng)表sysobjects中，并把創(chuàng)建的源代碼保存在系統(tǒng)表syscomments中 311. 使用系統(tǒng)存儲(chǔ)過

12、程 EXEC sp_helptext 例：使用sp_helptext 查看觸發(fā)器delete_trigger的定義文本信息USE pubsEXEC sp_helptext delete_triggergo3233 可以通過指定WITH ENCRYPTION來對(duì)觸發(fā)器的定義文本信息進(jìn)行加密，加密后的觸發(fā)器無法用sp_helptext查看相關(guān)信息34還可使用系統(tǒng)存儲(chǔ)過程sp_helptrigger來查看某特定表上存在的觸發(fā)器的某些信息EXEC sp_helptrigger 35例：用系統(tǒng)存儲(chǔ)過程sp_helptrigger 查看表authors上存在的所有觸發(fā)器的相關(guān)信息USE pubsEXEC

13、sp_helptrigger authorsgo36372. 使用系統(tǒng)表 例：用系統(tǒng)表sysobjects查看數(shù)據(jù)庫pubs上的所有觸發(fā)器的相關(guān)信息USE pubsSELECT name from sysobjectsWHERE type=TRgo388.3.1 使用觸發(fā)器強(qiáng)制數(shù)據(jù)完整性約束和觸發(fā)器都可以用來實(shí)施數(shù)據(jù)完整性，但兩者各有優(yōu)勢(shì)觸發(fā)器的可以包含使用 T-SQL 代碼的復(fù)雜處理邏輯觸發(fā)器可以支持約束的所有功能使用觸發(fā)器39以下應(yīng)用場(chǎng)合應(yīng)考慮使用觸發(fā)器： 除非REFERENCES子句定義了級(jí)聯(lián)引用操作，否則FOREIGN KEY約束只能以與另一列中的值完全匹配的值來驗(yàn)證列值 應(yīng)用程序要求

14、根據(jù)另一表中的列驗(yàn)證列值應(yīng)用程序要求使用自定義信息和較為復(fù)雜的錯(cuò)誤處理 408.3.2 使用觸發(fā)器強(qiáng)制業(yè)務(wù)規(guī)則 觸發(fā)器在強(qiáng)制數(shù)據(jù)完整性之外，還可強(qiáng)制實(shí)施對(duì)CHECK約束來說過于復(fù)雜的業(yè)務(wù)規(guī)則，包括對(duì)其他表中行的狀態(tài)進(jìn)行檢查41例：在Northwind數(shù)據(jù)庫中的Customers上建立DELETE觸發(fā)器delete_customers，使得在刪除表Customers中記錄的同時(shí)，自動(dòng)檢查表Orders中是否有該客戶的記錄，如果存在該客戶記錄，則取消刪除。42USE NorthwindgoCREATE TRIGGER delete_customersON dbo.CustomersFOR DELE

15、TEAS IF(SELECT COUNT(*) FROMOrders INNER JOIN DeletedON Orders.CustomerID= Deleted.CustomerID)043BEGINRAISERROR(You can not delete the customer with the order record.The transaction will be cancelled,10,1)ROLLBACK TRANSACTIONEND 448.4 修改和刪除觸發(fā)器 8.4.1 修改觸發(fā)器 ALTER TRIGGER owner.trigge_nameON owner.tabl

16、e|viewFOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETEWITH ENCRYPTIONAS45IF UPDATE(column_name)and|or UPDATE(column_name) sql_statesments ALTER TRIGGER owner.trigge_nameON owner.table|viewFOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETEWITH ENCRYPTIONAS IF UPDATE(column_name)and|or UPDATE(column_name) sql_statesments 46例：修改Northwind庫中的