數(shù)據(jù)庫(kù)原理與應(yīng)用：第15章 觸發(fā)器

數(shù)據(jù)庫(kù)原理與應(yīng)用15觸發(fā)器觸發(fā)器是一種特殊的存儲(chǔ)過(guò)程，其特殊性在于它并不需由用戶來(lái)直接調(diào)用，當(dāng)對(duì)表進(jìn)行插入、修改、刪除操作時(shí)自動(dòng)執(zhí)行。所以，觸發(fā)器可以用來(lái)實(shí)施復(fù)雜的完整性約束。觸發(fā)器的優(yōu)點(diǎn)可實(shí)現(xiàn)數(shù)據(jù)庫(kù)中多張表的級(jí)聯(lián)更新。實(shí)現(xiàn)比CHECK約束更為復(fù)雜的約束。使用CHECK約束，可以限制不滿足檢查條件的記錄輸入表中。CHECK約束的檢查條件表達(dá)式不允許引用其它表中的字段，而觸發(fā)器可引用其它表中的字段。觸發(fā)器的觸發(fā)方式SQLServer2000按觸發(fā)器被激活的時(shí)機(jī)可分為后觸發(fā)和替代觸發(fā)兩種觸發(fā)方式。后觸發(fā)：當(dāng)引起觸發(fā)器執(zhí)行的更新語(yǔ)句執(zhí)行完成，并通過(guò)各種約束檢查后，才執(zhí)行后觸發(fā)，這種觸發(fā)方式稱做后觸發(fā)。創(chuàng)建這種觸發(fā)器時(shí)用AFTER或FOR關(guān)鍵字來(lái)指定。后觸發(fā)只能創(chuàng)建在表上，而不能創(chuàng)建在視圖上。替代觸發(fā)：引起觸發(fā)器執(zhí)行的更新語(yǔ)句停止執(zhí)行，僅執(zhí)行觸發(fā)器語(yǔ)句，這種觸發(fā)方式稱做替代觸發(fā)。創(chuàng)建這種觸發(fā)器時(shí)用INSTEADOF關(guān)鍵字來(lái)指定。替代觸發(fā)可以創(chuàng)建在表或視圖上。inserted臨時(shí)表和deleted臨時(shí)表每個(gè)觸發(fā)器被激活時(shí)，系統(tǒng)都會(huì)為它自動(dòng)創(chuàng)建兩個(gè)臨時(shí)表：inserted表和deleted表。這兩個(gè)表的結(jié)構(gòu)總是與激活觸發(fā)器的表的結(jié)構(gòu)相同，觸發(fā)器執(zhí)行完成后，與該觸發(fā)器相關(guān)的這兩個(gè)臨時(shí)表也會(huì)被自動(dòng)刪除。在執(zhí)行DELETE語(yǔ)句刪除表中的數(shù)據(jù)時(shí)，系統(tǒng)將數(shù)據(jù)從表中刪除的同時(shí)，自動(dòng)把刪除的數(shù)據(jù)插入到deleted這一系統(tǒng)臨時(shí)表中；當(dāng)執(zhí)行INSERT語(yǔ)句向表中插入數(shù)據(jù)時(shí)，系統(tǒng)將數(shù)據(jù)插入表的同時(shí)，也把相應(yīng)的數(shù)據(jù)插入到inserted這一系統(tǒng)臨時(shí)表中；在執(zhí)行UPDATE語(yǔ)句修改表數(shù)據(jù)時(shí)，系統(tǒng)先從表中刪除表中原有的行，然后再插入新行。其中被刪除的行存放在deleted表中，同時(shí)插入的新行存放在inserted表中。注意：CREATETRIGGER語(yǔ)句必須是一個(gè)批處理中的第一條語(yǔ)句。觸發(fā)器只能在當(dāng)前的數(shù)據(jù)庫(kù)創(chuàng)建，但觸發(fā)器可能引用其它數(shù)據(jù)對(duì)象。如果指定觸發(fā)器所有者名限定觸發(fā)器，要以相同方式限定表名。在同一CREATETRIGGER語(yǔ)句中，可以為多種操作定義相同的觸發(fā)器操作。如果一個(gè)表的外鍵在DELETE、UPDATE操作上定義了級(jí)聯(lián)，則不能在該表上定義INSTEADOFDELETE、INSTEADOFUPDATE觸發(fā)器。所有建立和修改數(shù)據(jù)庫(kù)及其對(duì)象的語(yǔ)句、所有DROP語(yǔ)句都不允許在觸發(fā)器使用。觸發(fā)器不返回任何結(jié)果，為了阻止從觸發(fā)器返回結(jié)果，不要在觸發(fā)器定義中包含SELECT語(yǔ)句或變量賦值。如果必須在觸發(fā)器中進(jìn)行變量賦值，則應(yīng)該在觸發(fā)器的開(kāi)頭使用SETNOCOUNTON語(yǔ)句以避免返回任何結(jié)果集。15.1利用SQL命令創(chuàng)建觸發(fā)器

1.語(yǔ)法格式CREATETRIGGERtrigger_nameON{table|view}/*指定觸發(fā)器名及操作對(duì)象*/[WITHENCRYPTION] /*說(shuō)明是否采用加密方式*/{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}/*定義觸發(fā)器的類型*/ [NOTFORREPLICATION] /*說(shuō)明該觸發(fā)器不用于復(fù)制*/AS

[{IFUPDATE(column)

[{AND|OR}UPDATE(column)

]

[...n]

|IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask)

{comparison_operator}column_bitmask[...n]

}] /*兩個(gè)IF子句用于說(shuō)明觸發(fā)器執(zhí)行的條件*/

sql_statements /*T-SQL語(yǔ)句序列*/15.1利用SQL命令創(chuàng)建觸發(fā)器

例15.1對(duì)于school數(shù)據(jù)庫(kù)，如果在student表中添加或更改數(shù)據(jù)，則向客戶端顯示一條信息。/*使用帶有提示消息的觸發(fā)器*/USEXSBOOKIFEXISTS(SELECTnameFROMsysobjectsWHEREname='reminder'ANDtype='TR')DROPTRIGGERreminderGOCREATETRIGGERreminderONstudentFORINSERT,UPDATEASRAISERROR(13001,16,10)GO錯(cuò)誤號(hào)，取值13000—2147483647錯(cuò)誤級(jí)別，取值0—18狀態(tài)，取值1—127例15.2在數(shù)據(jù)庫(kù)school中創(chuàng)建一觸發(fā)器，當(dāng)向score表中插入一記錄時(shí)，檢查該記錄的sno在student表是否存在，檢查cno號(hào)在course表中是否存在，若有一項(xiàng)為否，則不允許插入，并返回“數(shù)據(jù)不一致”錯(cuò)誤信息。CREATETRIGGERTR_scoreONscoreFORINSERTASIFNOTEXISTS(SELECT*FROMstudentWHEREsno=(SELECTsnoFROMinserted))ORNOTEXISTS(SELECT*FROMcourseWHEREcno=(SELECTcnoFROMinserted))BEGINRAISERROR('數(shù)據(jù)不一致',16,1)

ROLLBACKTRANSACTIONEND例15.3在school數(shù)據(jù)庫(kù)的score表上創(chuàng)建一觸發(fā)器，若對(duì)sno列和cno列修改，則給出提示信息，并取消修改操作，用兩種方法實(shí)現(xiàn)。方法1：用IFUPDATE(column)CREATETRIGGERTR1_scoreONscoreFORUPDATEASIFUPDATE(sno)ORUPDATE(cno)BEGINRAISERROR('不允許修改',16,1)ROLLBACKTRANSACTIONEND方法2：用IFCOLUMNS_UPDATED()函數(shù)CREATETRIGGERTR2_scoreONscoreFORUPDATEASIF(COLUMNS_UPDATED()&3)>0BEGINRAISERROR('不允許修改',16,1)ROLLBACKTRANSACTIONEND15.1利用SQL命令創(chuàng)建觸發(fā)器INSTEADOF觸發(fā)器的設(shè)計(jì)CREATEVIEWVIEW2ASSELECTsno,sname,ssex,sbirthday,classFROMstudentCREATETRIGGERINSTEADOFTR_VIEW2ONVIEW2INSTEADOFINSERTASBEGININSERTINTOstudent(sno,sname,class)SELECTsno,sname,classFROMinsertedENDINSERTINTOVIEW2VALUES('120','王明','男','1975-5-5','95031')向score表插入一條學(xué)生選課記錄，如果該記錄學(xué)生成績(jī)低于60分，則把他成績(jī)改為60分。CREATETRIGGERS1ONscoreFORINSERTASBEGINDECLARE@num1char(5),@num2char(10)IF(SELECTdegreeFROMinserted)<60BEGINSET@num1=(SELECTsnoFROMinserted)SET@num2=(SELECTcnoFROMinserted)UPDATEscoreSETdegree=60WHEREsno=@num1ANDcno=@num2ENDENDCREATETRIGGERS2ONscoreINSTEADOFINSERTASBEGINDECLARE@num1char(10),@num2char(10)IF(SELECTdegreeFROMinserted)<60BEGINSET@num1=(SELECTsnoFROMinserted)SET@num2=(SELECTcnoFROMinserted)INSERTINTOscoreVALUES(@num1,@num2,60)ENDENDINSERTINTOscoreVALUES('95001','5',50)INSERTINTOscoreVALUES('95001','6',99)CREATETRIGGERS2ONscoreINSTEADOFINSERTASBEGINDECLARE@num1char(10),@num2char(10)IF(SELECTdegreeFROMinserted)<60BEGINSET@num1=(SELECTsnoFROMinserted)SET@num2=(SELECTcnoFROMinserted)INSERTINTOscoreVALUES(@num1,@num2,60)ENDELSEINSERTINTOscoreSELECT*FROMinsertedEND15.2利用企業(yè)管理器創(chuàng)建觸發(fā)器

要對(duì)school數(shù)據(jù)庫(kù)的score表定義DELETE觸發(fā)器，利用企業(yè)管理器實(shí)現(xiàn)步驟如下：

(1)進(jìn)入企業(yè)管理器的目錄樹(shù)，展開(kāi)school數(shù)據(jù)庫(kù)的文件夾，選中score表的圖標(biāo)右擊，出現(xiàn)一快捷菜單，選擇“所有任務(wù)”菜單項(xiàng)下的“管理觸發(fā)器”子菜單項(xiàng)，進(jìn)入觸發(fā)器屬性面。(2)在“名稱”中，單擊“新建”，刪除文本框中系統(tǒng)自動(dòng)產(chǎn)生的內(nèi)容，然后輸入觸發(fā)器的代碼：15.3觸發(fā)器的修改1.利用SQL命令修改觸發(fā)器語(yǔ)法格式：ALTERTRIGGERtrigger_nameON(table|view)[WITHENCRYPTION]{(FOR|AFTER|INSTEADOF){[DELETE][,][INSERT][,][UPDATE]}

[NOTFORREPLICATION]

AS

sql_statements

}

|{(FOR|AFTER|INSTEADOF){[INSERT][,][UPDATE]}

[NOTFORREPLICATION]

AS

{IFUPDATE(column)

[{AND|OR}UPDATE(column)]

[...n]

|IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask){comparison_operator}column_bitmask[...n]

}

sql_statements