觸發(fā)器數(shù)據(jù)庫

觸發(fā)器數(shù)據(jù)庫1第1頁，共56頁，2023年，2月20日，星期四7.1.1觸發(fā)器的基本概念7.1觸發(fā)器的基本概念和優(yōu)點(diǎn)數(shù)據(jù)庫觸發(fā)器是一種在基表被修改時(shí)自動執(zhí)行的內(nèi)嵌過程，當(dāng)使用UPDATE、INSERT或DELETE命令在指定表中對數(shù)據(jù)進(jìn)行修改時(shí)，用來防止對數(shù)據(jù)進(jìn)行的不正確或不一致的修改2第2頁，共56頁，2023年，2月20日，星期四通過觸發(fā)器可以把事務(wù)規(guī)則從應(yīng)用程序代碼移到數(shù)據(jù)庫中從而確保事務(wù)規(guī)則被遵守，并能顯著提高性能3第3頁，共56頁，2023年，2月20日，星期四7.1.2使用觸發(fā)器的優(yōu)點(diǎn)1.強(qiáng)制比CHCEK約束更復(fù)雜的數(shù)據(jù)完整性在CHECK約束中不允許引用其他表中的列來完成檢查工作，而觸發(fā)器則可以引用其他表中的列來完成數(shù)據(jù)完整性的約束

進(jìn)出貨：存貨量>定購量4第4頁，共56頁，2023年，2月20日，星期四2.使用自定義的錯(cuò)誤信息用戶有時(shí)需要在數(shù)據(jù)完整性遭到破壞或其他情況下，發(fā)出預(yù)先自定義好的錯(cuò)誤信息或動態(tài)自定義的錯(cuò)誤信息5第5頁，共56頁，2023年，2月20日，星期四3.實(shí)現(xiàn)數(shù)據(jù)庫中多張表的級聯(lián)修改在titleauthor、sales及roysched表中對各匹配行進(jìn)行定位刪除title_id列titles表在title_id列上定義一個(gè)刪除觸發(fā)器titleauthor表sales表roysched表此三列均有title_id字段6第6頁，共56頁，2023年，2月20日，星期四4.比較數(shù)據(jù)庫修改前后數(shù)據(jù)的狀態(tài)用戶可在觸發(fā)器中引用由于修改所影響的記錄行觸發(fā)器提供訪問由INSERT、UPDATE或DELETE語句引起的數(shù)據(jù)變化的前后狀態(tài)7第7頁，共56頁，2023年，2月20日，星期四5.維護(hù)非規(guī)范化數(shù)據(jù)非規(guī)范數(shù)據(jù)通常是指在表中的派生的、冗余的數(shù)據(jù)值維護(hù)非規(guī)范化數(shù)據(jù)應(yīng)該通過使用觸發(fā)器來實(shí)現(xiàn)表的級聯(lián)是指不同表之間的主外鍵關(guān)系，維護(hù)表的級聯(lián)可通過設(shè)置表的主鍵與外鍵的關(guān)系來實(shí)現(xiàn)注意區(qū)別8第8頁，共56頁，2023年，2月20日，星期四觸發(fā)器類型：分為兩種：AFTER觸發(fā)器和INSTEADOF觸發(fā)器AFTER觸發(fā)器：這種觸發(fā)器將在數(shù)據(jù)變動(insert,update,delete操作)完成以后才觸發(fā)。對變動的數(shù)據(jù)進(jìn)行檢查，如果發(fā)現(xiàn)錯(cuò)誤，則拒絕或回滾變動的數(shù)據(jù)。INSTEADOF觸發(fā)器：這種觸發(fā)器將在數(shù)據(jù)變動以前被觸發(fā)。并取代變動數(shù)據(jù)的操作(insert,update,delete操作)，轉(zhuǎn)而去執(zhí)行觸發(fā)器定義的操作。在建立觸發(fā)器時(shí)，還必須指定觸發(fā)操作：insert、update、delete操作，至少指定一種，也可指定多種。9第9頁，共56頁，2023年，2月20日，星期四觸發(fā)事件發(fā)生After條件觸發(fā)器執(zhí)行示意圖執(zhí)行觸發(fā)事件執(zhí)行觸發(fā)器動作觸發(fā)事件發(fā)生Insteadof條件觸發(fā)器執(zhí)行示意圖不執(zhí)行觸發(fā)事件執(zhí)行觸發(fā)器動作10第10頁，共56頁，2023年，2月20日，星期四觸發(fā)器結(jié)構(gòu)：事件UPDATEINSERTDELETE條件AFTERINSTEADOF動作各種Transact-SQL語句有時(shí)，觸發(fā)器也稱主動規(guī)則，或稱事件—條件—動作規(guī)則(ECA)11第11頁，共56頁，2023年，2月20日，星期四7.2觸發(fā)器的創(chuàng)建CREATETRIGGERtrigger_name

ON{table|view}

[WITHENCRYPTION]

{

{{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}

[WITHAPPEND]

[NOTFORREPLICATION]

AS12第12頁，共56頁，2023年，2月20日，星期四[{IFUPDATE(column)

[{AND|OR}UPDATE(column)]

[...n]

|IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask)

{comparison_operator}column_bitmask[...n]

}]

sql_statement[...n]

}

}

SQLServer不支持在觸發(fā)器中包含所有的create語句、DROP語句等13第13頁，共56頁，2023年，2月20日，星期四創(chuàng)建觸發(fā)器在同一個(gè)數(shù)據(jù)表中可以創(chuàng)建多個(gè)after觸發(fā)器，在表或視圖上，每個(gè)insert、update、delete語句最多可以定義一個(gè)insteadof觸發(fā)器。14第14頁，共56頁，2023年，2月20日，星期四創(chuàng)建觸發(fā)器應(yīng)該考慮以下幾個(gè)問題：CREATETRIGGER語句必須是批處理中的第一個(gè)語句。創(chuàng)建觸發(fā)器的權(quán)限默認(rèn)分配給表的所有者，且不能將該權(quán)限轉(zhuǎn)給其他用戶。觸發(fā)器為數(shù)據(jù)庫對象，其名稱必須遵循標(biāo)識符的命名規(guī)則。雖然觸發(fā)器可以引用當(dāng)前數(shù)據(jù)庫以外的對象，但只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建觸發(fā)器。15第15頁，共56頁，2023年，2月20日，星期四創(chuàng)建觸發(fā)器應(yīng)該考慮以下幾個(gè)問題：雖然不能在臨時(shí)表或系統(tǒng)表上創(chuàng)建觸發(fā)器，但是觸發(fā)器可以引用臨時(shí)表。在為某數(shù)據(jù)表創(chuàng)建外鍵約束時(shí),選中‘級聯(lián)刪除相關(guān)字段’，不能定義INSTEADOFDELETE觸發(fā)器；選中‘級聯(lián)更新相關(guān)字段’，不能定義INSTEADOFUPDATE觸發(fā)器。雖然TRUNCATETABLE語句類似于沒有WHERE子句（用于刪除行）的DELETE語句，但它并不會引發(fā)DELETE觸發(fā)器，因?yàn)門RUNCATETABLE語句沒有記錄。16第16頁，共56頁，2023年，2月20日，星期四7.2.1INSERT觸發(fā)器例：在pubs庫的authors表上創(chuàng)建my_trigger1觸發(fā)器，該觸發(fā)器被操作INSERT所觸發(fā)USEpubsgoCREATETRIGGERmy_trigger1ONauthorsFORINSERTASraiserror('unauthorized',10,1)17第17頁，共56頁，2023年，2月20日，星期四當(dāng)向表authors插入數(shù)據(jù)時(shí)將觸發(fā)觸發(fā)器，但是數(shù)據(jù)仍能被插入表中

INSERTINTOauthorsvalues(‘172-33-1234','White','John','408496-7223','10932BiggeRd.','MenloPark','CA','94025',1)

定義觸發(fā)器時(shí)指定了FOR選項(xiàng)，AFTER為默認(rèn)值，觸發(fā)器只在INSERT中指定的操作都執(zhí)行后才激發(fā)，因此仍能插入數(shù)據(jù)18第18頁，共56頁，2023年，2月20日，星期四有沒有什么辦法能實(shí)現(xiàn)觸發(fā)器被執(zhí)行的同時(shí)，取消觸發(fā)觸發(fā)器的SQL語句的操作呢？用INSTEADOF關(guān)鍵字來實(shí)現(xiàn)19第19頁，共56頁，2023年，2月20日，星期四例:在pubs庫的authors表上創(chuàng)建觸發(fā)器my_trigger2，它被操作DELETE所觸發(fā)，且要求觸發(fā)觸發(fā)器的DELETE語句在執(zhí)行后被取消USEPubsGOCREATETRIGGERmy_trigger2ONauthorsINSTEADOFDELETEASraiserror('NoRightToDeleteIt’,10,1)20第20頁，共56頁，2023年，2月20日，星期四如果在表authors中刪除前面例子中新增的記錄，就會報(bào)錯(cuò)，如：

DELETE*fromauthorsWhereau_id=’172-33-1234’21第21頁，共56頁，2023年，2月20日，星期四7.2.2UPDATE觸發(fā)器通過定義IFUPDATE<COLUMN_NAME>實(shí)現(xiàn)當(dāng)特定列被更新時(shí)觸發(fā)觸發(fā)器不論該更新影響表中的多少行若用戶需要實(shí)現(xiàn)多個(gè)特定列中的任意一列被更新時(shí)觸發(fā)觸發(fā)器，可以通過在觸發(fā)器定義中使用多個(gè)IFUPDATE<COLUMN_NAME>語句來實(shí)現(xiàn)22第22頁，共56頁，2023年，2月20日，星期四例:在pubs庫的authors表上建觸發(fā)器member_trigger，它將被UPDATE操作激活,且不允許修改au_lname字段USEpubsgoCREATETRIGGERmember_triggerONauthorsFORUPDATEAS23第23頁，共56頁，2023年，2月20日，星期四不使用INSTEADOF而是通過rollbacktransaction子句恢復(fù)原來的數(shù)據(jù)的方法來實(shí)現(xiàn)字段不被修改IFUPDATE(au_lname)BEGINraiserror('Unauthorized!',10,1)

rollbacktransactionEND24第24頁，共56頁，2023年，2月20日，星期四建好觸發(fā)器后試著執(zhí)行UPDATE操作

USEpubsgoUPDATEauthorsSETau_lname='Wangti'WHEREau_lname='White'

運(yùn)行結(jié)果顯示：“Unauthorized!”

25第25頁，共56頁，2023年，2月20日，星期四在“查詢分析器”中運(yùn)行如下命令：USEpubsgoSELECTau_lnameFROMauthorsWHEREau_lnameLIKE'W%'查詢結(jié)果中只有兩行的White的內(nèi)容，可見剛才的更新操作并不能實(shí)現(xiàn)對表中au_lname字段的更新26第26頁，共56頁，2023年，2月20日，星期四UPDATE可對未建立保護(hù)性觸發(fā)的字段進(jìn)行更新而不激發(fā)觸發(fā)器，例如：USEpubsgoUPDATEauthorsSETau_fname='Wangti'WHEREau_fname='Johnson'檢索表authors可看到表內(nèi)的信息被更新了，這是由于在au_fname字段上未建立UPDATE的觸發(fā)返回消息：“所影響的行數(shù)為1行”27第27頁，共56頁，2023年，2月20日，星期四7.2.3DELETE觸發(fā)器利用DELETE觸發(fā)器，能在相應(yīng)的表中實(shí)現(xiàn)當(dāng)遇到刪除動作時(shí)自動發(fā)出報(bào)警28第28頁，共56頁，2023年，2月20日，星期四例：在pubs庫的sales表上建立名為delete_trigger的DELETE觸發(fā)器，實(shí)現(xiàn)對刪除sales表中銷售記錄的操作給出報(bào)警，并取消當(dāng)前的刪除操作USEpubsgoCREATETRIGGERdelete_triggerONsalesFORDELETEASBEGINraiserror('Unauthorized!',10,1)rollbacktransactionEND29第29頁，共56頁，2023年，2月20日，星期四7.2.4查看觸發(fā)器信息象存儲過程一樣，觸發(fā)器在創(chuàng)建后，系統(tǒng)把觸發(fā)器的名稱保存在系統(tǒng)表sysobjects中，并把創(chuàng)建的源代碼保存在系統(tǒng)表syscomments中30第30頁，共56頁，2023年，2月20日，星期四1.使用系統(tǒng)存儲過程EXECsp_helptext<triggername>例：使用sp_helptext查看觸發(fā)器delete_trigger的定義文本信息USEpubsEXECsp_helptextdelete_triggergo31第31頁，共56頁，2023年，2月20日，星期四32第32頁，共56頁，2023年，2月20日，星期四可以通過指定WITH

ENCRYPTION來對觸發(fā)器的定義文本信息進(jìn)行加密，加密后的觸發(fā)器無法用sp_helptext查看相關(guān)信息33第33頁，共56頁，2023年，2月20日，星期四還可使用系統(tǒng)存儲過程sp_helptrigger來查看某特定表上存在的觸發(fā)器的某些信息

EXECsp_helptrigger<tablename>

34第34頁，共56頁，2023年，2月20日，星期四例：用系統(tǒng)存儲過程sp_helptrigger查看表authors上存在的所有觸發(fā)器的相關(guān)信息

USEpubsEXECsp_helptriggerauthorsgo35第35頁，共56頁，2023年，2月20日，星期四36第36頁，共56頁，2023年，2月20日，星期四2.使用系統(tǒng)表例：用系統(tǒng)表sysobjects查看數(shù)據(jù)庫pubs上的所有觸發(fā)器的相關(guān)信息

USEpubsSELECTnamefromsysobjectsWHEREtype='TR'go37第37頁，共56頁，2023年，2月20日，星期四3.用“企業(yè)管理器”查看觸發(fā)器38第38頁，共56頁，2023年，2月20日，星期四7.3.1使用觸發(fā)器強(qiáng)制數(shù)據(jù)完整性約束和觸發(fā)器都可以用來實(shí)施數(shù)據(jù)完整性，但兩者各有優(yōu)勢觸發(fā)器的可以包含使用T-SQL

代碼的復(fù)雜處理邏輯觸發(fā)器可以支持約束的所有功能39第39頁，共56頁，2023年，2月20日，星期四實(shí)體完整性應(yīng)在最低級別上通過索引進(jìn)行強(qiáng)制，這些索引或是PRIMARY

KEY和UNIQUE約束的一部分，或是在約束之外獨(dú)立創(chuàng)建的域完整性應(yīng)通過CHECK約束來強(qiáng)制引用完整性應(yīng)通過FOREIGN

KEY

約束來強(qiáng)制40第40頁，共56頁，2023年，2月20日，星期四以下應(yīng)用場合應(yīng)考慮使用觸發(fā)器：除非REFERENCES子句定義了級聯(lián)引用操作，否則FOREIGNKEY約束只能以與另一列中的值完全匹配的值來驗(yàn)證列值應(yīng)用程序要求根據(jù)另一表中的列驗(yàn)證列值應(yīng)用程序要求使用自定義信息和較為復(fù)雜的錯(cuò)誤處理41第41頁，共56頁，2023年，2月20日，星期四7.3.2使用觸發(fā)器強(qiáng)制業(yè)務(wù)規(guī)則觸發(fā)器在強(qiáng)制數(shù)據(jù)完整性之外，還可強(qiáng)制實(shí)施對CHECK約束來說過于復(fù)雜的業(yè)務(wù)規(guī)則，包括對其他表中行的狀態(tài)進(jìn)行檢查42第42頁，共56頁，2023年，2月20日，星期四例：在Northwind數(shù)據(jù)庫中的Customers上建立DELETE觸發(fā)器delete_customers，使得在刪除表Customers中記錄的同時(shí)，自動檢查表Orders中是否有該客戶的記錄，如果存在該客戶記錄，則取消刪除。43第43頁，共56頁，2023年，2月20日，星期四USENorthwindgoCREATE

TRIGGERdelete_customersONdbo.[Customers]FORDELETEASIF(SELECT

COUNT(*)FROM [Orders]INNER

JOIN

DeletedON[Orders].CustomerID= Deleted.CustomerID)>044第44頁，共56頁，2023年，2月20日，星期四BEGINRAISERROR(‘Youcannotdeletethecustomerwiththeorderrecord.\ Thetransactionwillbecancelled’,10,1)ROLLBACK

TRANSACTIONEND

45第45頁，共56頁，2023年，2月20日，星期四7.4修改和刪除觸發(fā)器7.4.1修改觸發(fā)器ALTER

TRIGGER[owner.]trigge_nameON[owner.]{table|view}{FOR|AFTER|INSTEAD

OF}{INSERT,UPDATE,DELETE}[WITH

ENCRYPTION]AS46第46頁，共56頁，2023年，2月20日，星期四IF

UPDATE(column_name)[{and|or}UPDATE(column_name)…]sql_statesmentsALTER

TRIGGER

[owner.]trigge_nameON[owner.]{table|view}{FOR|AFTER|INSTEADOF}{INSERT,UPDATE,DELETE}[WITHENCRYPTION]ASIFUPDATE(column_name)[{and|or}UPDATE(column_name)…]sql_statesments47第47頁，共56頁，2023年，2月20日，星期四例：修改Northwind庫中的視圖Customers上的INSTEADOF觸發(fā)器delete_customers，使得用戶在該視圖上執(zhí)行除刪除外的增加，修改操作時(shí)，也自動給出錯(cuò)誤提示信息并撤銷此次操作48第48頁，共56頁，2023年，2月20日，星期四USENorthwindgoALTER

TRIGGERdelete_customersONCustomersINSTEADOF

DELETE,INSERT,UPDATEASRAISERROR('Youcannotinsert,deleteorupdaterecordsfromthisview',10,1)

49第49頁，共56頁，2023年，2月