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

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

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

[{IFUPDATE(column)

[{AND|OR}UPDATE(column)

]

[...n]

|IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask)

{comparison_operator}column_bitmask[...n]

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

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

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

ROLLBACKTRANSACTIONEND例15.3在school數(shù)據(jù)庫的score表上創(chuàng)建一觸發(fā)器，若對sno列和cno列修改，則給出提示信息，并取消修改操作，用兩種方法實現(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ā)器的設計CREATEVIEWVIEW2ASSELECTsno,sname,ssex,sbirthday,classFROMstudentCREATETRIGGERINSTEADOFTR_VIEW2ONVIEW2INSTEADOFINSERTASBEGININSERTINTOstudent(sno,sname,class)SELECTsno,sname,classFROMinsertedENDINSERTINTOVIEW2VALUES('120','王明','男','1975-5-5','95031')向score表插入一條學生選課記錄，如果該記錄學生成績低于60分，則把他成績改為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ā)器

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

(1)進入企業(yè)管理器的目錄樹，展開school數(shù)據(jù)庫的文件夾，選中score表的圖標右擊，出現(xiàn)一快捷菜單，選擇“所有任務”菜單項下的“管理觸發(fā)器”子菜單項，進入觸發(fā)器屬性面。(2)在“名稱”中，單擊“新建”，刪除文本框中系統(tǒng)自動產(chǎn)生的內(nèi)容，然后輸入觸發(fā)器的代碼：15.3觸發(fā)器的修改1.利用SQL命令修改觸發(fā)器語法格式：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