1、SQL觸發器語法參考CreateTRIGGERtrigger_nameONtable|viewWITHENCRYPTIONFOR|AFTER|INSTEADOFInsert,UpdateWITHAPPENDNOTFORREPLICATIONASIFUpdate(column)AND|orUpdate(column).n|IF(COLUMNS_UpdateD()bitwise_operatorupdated_bitmask)comparison_operatorcolumn_bitmask.nsql_statement.n參數trigger_name是觸發器的名稱。觸發器名稱必須符合標識符規則，

2、并且在數據庫中必須唯一。可以選擇是否指定觸發器所有者名稱。Table|view是在其上執行觸發器的表或視圖，有時稱為觸發器表或觸發器視圖。可以選擇是否指定表或視圖的所有者名稱。WITHENCRYPTION加密syscomments表中包含CreateTRIGGER語句文本的條目。使用WITHENCRYPTION可防止將觸發器作為SQLServer復制的一部分發布。AFTER指定觸發器只有在觸發SQL語句中指定的所有操作都已成功執行后才激發。所有的引用級聯操作和約束檢查也必須成功完成后，才能執行此觸發器。如果僅指定FOR關鍵字，則AFTER是默認設置。不能在視圖上定義AFTER觸發器。INSTE

3、ADOF指定執行觸發器而不是執行觸發SQL語句，從而替代觸發語句的操作。在表或視圖上，每個Insert、Update或Delete語句最多可以定義一個INSTEADOF觸發器。然而，可以在每個具有INSTEADOF觸發器的視圖上定義視圖。INSTEADOF觸發器不能在WITHCHECKOPTION的可更新視圖上定義。如果向指定了WITHCHECKOPTION選項的可更新視圖添加INSTEADOF觸發器，SQLServer將產生一個錯誤。用戶必須用AlterVIEW刪除該選項后才能定義INSTEADOF觸發器。Delete,Insert,Update是指定在表或視圖上執行哪些數據修改語句時將激活

4、觸發器的關鍵字。必須至少指定一個選項。在觸發器定義中允許使用以任意順序組合的這些關鍵字。如果指定的選項多于一個，需用逗號分隔這些選項。對于INSTEADOF觸發器，不允許在具有ONDelete級聯操作引用關系的表上使用Delete選項。同樣，也不允許在具有ONUpdate級聯操作引用關系的表上使用Update選項。WITHAPPEND指定應該添加現有類型的其它觸發器。只有當兼容級別是65或更低時，才需要使用該可選子句。如果兼容級別是70或更高，則不必使用WITHAPPEND子句添加現有類型的其它觸發器（這是兼容級別設置為70或更高的CreateTRIGGER的默認行為）。有關更多信息，請參見s

5、p_dbcmptlevel。WITHAPPEND不能與INSTEADOF觸發器一起使用，或者，如果顯式聲明AFTER觸發器，也不能使用該子句。只有當出于向后兼容而指定FOR時（沒有INSTEADOF或AFTER），才能使用WITHAPPEND。以后的版本將不支持WITHAPPEND和FOR（將被解釋為AFTER）。NOTFORREPLICATION表示當復制進程更改觸發器所涉及的表時，不應執行該觸發器。AS是觸發器要執行的操作。sql_statement是觸發器的條件和操作。觸發器條件指定其它準則，以確定Delete、Insert或Update語句是否導致執行觸發器操作。當嘗試Delete、I

6、nsert或Update操作時，Transact-SQL語句中指定的觸發器操作將生效。觸發器可以包含任意數量和種類的Transact-SQL語句。觸發器旨在根據數據修改語句檢查或更改數據；它不應將數據返回給用戶。觸發器中的Transact-SQL語句常常包含控制流語言。CreateTRIGGER語句中使用幾個特殊的表：deleted和inserted是邏輯（概念）表。這些表在結構上類似于定義觸發器的表（也就是在其中嘗試用戶操作的表）；這些表用于保存用戶操作可能更改的行的舊值或新值。例如，若要檢索deleted表中的所有值，請使用：Select*FROMdeleted如果兼容級別等于70，那么在

7、Delete、Insert或Update觸發器中，SQLServer將不允許引用inserted和deleted表中的text、ntext或image列。不能訪問inserted和deleted表中的text、ntext和image值。若要在Insert或Update觸發器中檢索新值，請將inserted表與原始更新表聯接。當兼容級別是65或更低時，對inserted或deleted表中允許空值的text、ntext或image列，將返回空值；如果這些列不可為空，則返回零長度字符串。當兼容級別是80或更高時，SQLServer允許在表或視圖上通過INSTEADOF觸發器更新text、ntext

8、或image列。是表示觸發器中可以包含多條Transact-SQL語句的占位符。對于IFUpdate(column)語句，可以通過重復Update(column)子句包含多列。IFUpdate(column)測試在指定的列上進行的Insert或Update操作，不能用于Delete操作。可以指定多列。因為在ON子句中指定了表名，所以在IFUpdate子句中的列名前不要包含表名。若要測試在多個列上進行的Insert或Update操作，請在第一個操作后指定單獨的Update(column)子句。在Insert操作中IFUpdate將返回TRUE值，因為這些列插入了顯式值或隱性(NULL)值。說明I

9、FUpdate(column)子句的功能等同于IF、IF.ELSE或WHILE語句，并且可以使用BEGIN.END語句塊。有關更多信息，請參見控制流語言。可以在觸發器主體中的任意位置使用Update(column)。column是要測試Insert或Update操作的列名。該列可以是SQLServer支持的任何數據類型。但是，計算列不能用于該環境中。有關更多信息，請參見數據類型。IF(COLUMNS_UpdateD()測試是否插入或更新了提及的列，僅用于Insert或Update觸發器中。COLUMNS_UpdateD返回varbinary位模式，表示插入或更新了表中的哪些列。COLUMNS_

10、UpdateD函數以從左到右的順序返回位，最左邊的為最不重要的位。最左邊的位表示表中的第一列；向右的下一位表示第二列，依此類推。如果在表上創建的觸發器包含8列以上，則COLUMNS_UpdateD返回多個字節，最左邊的為最不重要的字節。在Insert操作中COLUMNS_UpdateD將對所有列返回TRUE值，因為這些列插入了顯式值或隱性(NULL)值。可以在觸發器主體中的任意位置使用COLUMNS_UpdateD。bitwise_operator是用于比較運算的位運算符。updated_bitmask是整型位掩碼，表示實際更新或插入的列。例如，表t1包含列C1、C2、C3、C4和C5。假定表

11、t1上有Updae觸發器，若要檢查列C2、C3和C4是否都有更新，指定值14；若要檢查是否只有列C2有更新，指定值2。comparison_operator是比較運算符。使用等號(=)檢查updated_bitmask中指定的所有列是否都實際進行了更新。使用大于號(>)檢查updated_bitmask中指定的任一列或某些列是否已更新。column_bitmask是要檢查的列的整型位掩碼，用來檢查是否已更新或插入了這些列。注釋觸發器常常用于強制業務規則和數據完整性。SQLServer通過表創建語句(AlterTABLE和CreateTABLE)提供聲明引用完整性(DRI)；但是DRI不提

12、供數據庫間的引用完整性。若要強制引用完整性(有關表的主鍵和外鍵之間關系的規則)，請使用主鍵和外鍵約束(AlterTABLE和CreateTABLE的PRIMARYKEY和FOREIGNKEY關鍵字）。如果觸發器表存在約束，則在INSTEADOF觸發器執行之后和AFTER觸發器執行之前檢查這些約束。如果違反了約束，則回滾INSTEADOF觸發器操作且不執行（激發）AFTER觸發器。可用sp_settriggerorder指定表上第一個和最后一個執行的AFTER觸發器。在表上只能為每個Insert、Update和Delete操作指定一個第一個執行和一個最后一個執行的AFTER觸發器。如果同一表上還

13、有其它AFTER觸發器，則這些觸發器將以隨機順序執行。如果AlterTRIGGER語句更改了第一個或最后一個觸發器，則將除去已修改觸發器上設置的第一個或最后一個特性，而且必須用sp_settriggerorder重置排序值。只有當觸發SQL語句（包括所有與更新或刪除的對象關聯的引用級聯操作和約束檢查）成功執行后，AFTER觸發器才會執行。AFTER觸發器檢查觸發語句的運行效果，以及所有由觸發語句引起的Update和Delete引用級聯操作的效果。觸發器限制CreateTRIGGER必須是批處理中的第一條語句，并且只能應用到一個表中。觸發器只能在當前的數據庫中創建，不過觸發器可以引用當前數據庫的

14、外部對象。如果指定觸發器所有者名稱以限定觸發器，請以相同的方式限定表名。在同一條CreateTRIGGER語句中，可以為多種用戶操作（如Insert和Update）定義相同的觸發器操作。如果一個表的外鍵在Delete/Update操作上定義了級聯，則不能在該表上定義INSTEADOFDelete/Update觸發器。在觸發器內可以指定任意的SET語句。所選擇的SET選項在觸發器執行期間有效，并在觸發器執行完后恢復到以前的設置。與使用存儲過程一樣，當觸發器激發時，將向調用應用程序返回結果。若要避免由于觸發器激發而向應用程序返回結果，請不要包含返回結果的Select語句，也不要包含在觸發器中進行變

15、量賦值的語句。包含向用戶返回結果的Select語句或進行變量賦值的語句的觸發器需要特殊處理；這些返回的結果必須寫入允許修改觸發器表的每個應用程序中。如果必須在觸發器中進行變量賦值，則應該在觸發器的開頭使用SETNOCOUNT語句以避免返回任何結果集。Delete觸發器不能捕獲TRUNCATETABLE語句。盡管TRUNCATETABLE語句實際上是沒有Where子句的Delete（它刪除所有行），但它是無日志記錄的，因而不能執行觸發器。因為TRUNCATETABLE語句的權限默認授予表所有者且不可轉讓，所以只有表所有者才需要考慮無意中用TRUNCATETABLE語句規避Delete觸發器的問題

16、。無論有日志記錄還是無日志記錄，WRITETEXT語句都不激活觸發器。觸發器中不允許以下Transact-SQL語句：AlterDATABASECreateDATABASEDISKINITDISKRESIZEDropDATABASELOADDATABASELOADLOGRECONFIGURERESTOREDATABASERESTORELOG說明由于SQLServer不支持系統表中的用戶定義觸發器，因此建議不要在系統表中創建用戶定義觸發器。多個觸發器SQLServer允許為每個數據修改事件（Delete、Insert或Update）創建多個觸發器。例如，如果對已有Update觸發器的表執行Cre

17、ateTRIGGERFORUpdate，則將創建另一個更新觸發器。在早期版本中，在每個表上，每個數據修改事件（Insert、Update或Delete）只允許有一個觸發器。說明如果觸發器名稱不同，則CreateTRIGGER（兼容級別為70）的默認行為是在現有的觸發器中添加其它觸發器。如果觸發器名稱相同，則SQLServer返回一條錯誤信息。但是，如果兼容級別等于或小于65，則使用CreateTRIGGER語句創建的新觸發器將替換同一類型的任何現有觸發器，即使觸發器名稱不同。有關更多信息，請參見sp_dbcmptlevel。遞歸觸發器當在sp_dboption中啟用recursivetrigg

18、ers設置時，SQLServer還允許觸發器的遞歸調用。遞歸觸發器允許發生兩種類型的遞歸：間接遞歸直接遞歸使用間接遞歸時，應用程序更新表T1，從而激發觸發器TR1，該觸發器更新表T2。在這種情況下，觸發器T2將激發并更新T1。使用直接遞歸時，應用程序更新表T1，從而激發觸發器TR1，該觸發器更新表T1。由于表T1被更新，觸發器TR1再次激發，依此類推。下例既使用了間接觸發器遞歸，又使用了直接觸發器遞歸。假定在表T1中定義了兩個更新觸發器TR1和TR2。觸發器TR1遞歸地更新表T1。Update語句使TR1和TR2各執行一次。而TR1的執行將觸發TR1（遞歸）和TR2的執行。給定觸發器的inse

19、rted和deleted表只包含與喚醒調用觸發器的Update語句相對應的行。說明只有啟用sp_dboption的recursivetriggers設置，才會發生上述行為。對于為給定事件定義的多個觸發器，并沒有確定的執行順序。每個觸發器都應是自包含的。禁用recursivetriggers設置只能禁止直接遞歸。若要也禁用間接遞歸，請使用sp_configure將nestedtriggers服務器選項設置為0。如果任一觸發器執行了ROLLBACKTRANSACTION語句，則無論嵌套級是多少，都不會進一步執行其它觸發器。嵌套觸發器觸發器最多可以嵌套32層。如果一個觸發器更改了包含另一個觸發器的表

20、，則第二個觸發器將激活，然后該觸發器可以再調用第三個觸發器，依此類推。如果鏈中任意一個觸發器引發了無限循環，則會超出嵌套級限制，從而導致取消觸發器。若要禁用嵌套觸發器，請用sp_configure將nestedtriggers選項設置為0（關閉）。默認配置允許嵌套觸發器。如果嵌套觸發器是關閉的，則也將禁用遞歸觸發器，與sp_dboption的recursivetriggers設置無關。延遲名稱解析SQLServer允許Transact-SQL存儲過程、觸發器和批處理引用編譯時不存在的表。這種能力稱為延遲名稱解析。但是，如果Transact-SQL存儲過程、觸發器或批處理引用在存儲過程或觸發器中

21、定義的表，則只有當兼容級別設置（通過執行sp_dbcmptlevel設置）等于65時，才會在創建時發出警告。如果使用批處理，則在編譯時發出警告。如果引用的表不存在，將在運行時返回錯誤信息。有關更多信息，請參見延遲名稱解析和編譯。權限CreateTRIGGER權限默認授予定義觸發器的表所有者、sysadmin固定服務器角色成員以及db_owner和db_ddladmin固定數據庫角色成員，并且不可轉讓。若要檢索表或視圖中的數據，用戶必須在表或視圖中擁有Select語句權限。若要更新表或視圖的內容，用戶必須在表或視圖中擁有Insert、Delete和Update語句權限。如果視圖中存在INSTEA

22、DOF觸發器，用戶必須在該視圖中有Insert、Delete和Update特權，以對該視圖發出Insert、Delete和Update語句，而不管實際上是否在視圖上執行了這樣的操作。示例A.使用帶有提醒消息的觸發器當有人試圖在titles表中添加或更改數據時，下例將向客戶端顯示一條消息。說明消息50009是sysmessages中的用戶定義消息。有關創建用戶定義消息的更多信息，請參見sp_addmessage。USEpubsIFEXISTS(SelectnameFROMsysobjectsWherename='reminder'ANDtype='TR')Drop

23、TRIGGERreminderGOCreateTRIGGERreminderONtitlesFORInsert,UpdateASRAISERROR(50009,16,10)GOB. 使用帶有提醒電子郵件的觸發器當titles表更改時，下例將電子郵件發送給指定的人員(MaryM)。USEpubsIFEXISTS(SelectnameFROMsysobjectsWherename='reminder'ANDtype='TR')DropTRIGGERreminderGOCreateTRIGGERreminderONtitlesFORInsert,Update,Dele

24、teASEXECmaster.xp_sendmail'MaryM','Don''tforgettoprintareportforthedistributors.'GOC. 在employee和jobs表之間使用觸發器業務規則由于CHECK約束只能引用定義了列級或表級約束的列，表間的任何約束(在下例中是指業務規則)都必須定義為觸發器。下例創建一個觸發器，當插入或更新雇員工作級別(job_lvls)時，該觸發器檢查指定雇員的工作級別(由此決定薪水)是否處于為該工作定義的范圍內。若要獲得適當的范圍，必須引用jobs表。USEpubsIFEXISTS(S

25、electnameFROMsysobjectsWherename='employee_insupd'ANDtype='TR')DropTRIGGERemployee_insupdGOCreateTRIGGERemployee_insupdONemployeeFORInsert,UpdateAS/*Gettherangeoflevelforthisjobtypefromthejobstable.*/DECLAREmin_lvltinyint,max_lvltinyint,emp_lvltinyint,job_idsmallintSelectmin_lvl=min_

26、lvl,max_lvl=max_lvl,emp_lvl=i.job_lvl,job_id=i.job_idFROMemployeeeINNERJOINinsertediONe.emp_id=i.emp_idJOINjobsjONj.job_id=i.job_idIF(job_id=1)and(emp_lvl10)BEGINRAISERROR('Jobid1expectsthedefaultlevelof10.',16,1)ROLLBACKTRANSACTIONENDELSEIFNOT(emp_lvlBETWEENmin_lvlANDmax_lvl)BEGINRAISERROR(

27、'Thelevelforjob_id:%dshouldbebetween%dand%d.',16,1,job_id,min_lvl,max_lvl)ROLLBACKTRANSACTIONENDD. 使用延遲名稱解析下例創建兩個觸發器以說明延遲名稱解析。USEpubsIFEXISTS(SelectnameFROMsysobjectsWherename='trig1'ANDtype='TR')DropTRIGGERtrig1GO- -Creatingatriggeronanonexistenttable.CreateTRIGGERtrig1onaut

28、horsFORInsert,Update,DeleteASSelecta.au_lname,a.au_fname,FROMauthorsaINNERJOINdoes_not_existxONa.au_id=x.au_idGO- -Hereisthestatementtoactuallyseethetextofthetrigger.Selecto.id,c.textFROMsysobjectsoINNERJOINsyscommentscONo.id=c.idWhereo.type='TR'='trig1'- -Creatingatri

29、ggeronanexistingtable,butwithanonexistent- -column.USEpubsIFEXISTS(SelectnameFROMsysobjectsWherename='trig2'ANDtype='TR')DropTRIGGERtrig2GOCreateTRIGGERtrig2ONauthorsFORInsert,UpdateASDECLAREfaxvarchar(12)Selectfax=phoneFROMauthorsGO- -Hereisthestatementtoactuallyseethetextofthetrigg

30、er.Selecto.id,c.textFROMsysobjectsoINNERJOINsyscommentscONo.id=c.idWhereo.type='TR'='trig2'E. 使用COLUMNS_UpdateD下例創建兩個表：一個employeeData表和一個auditEmployeeData表。人力資源部的成員可以修改employeeData表，該表包含敏感的雇員薪水信息。如果更改了雇員的社會保險號碼(SSN)、年薪或銀行帳戶，則生成審核記錄并插入到auditEmployeeData審核表。通過使用COLUMNS_UpdateD

31、()功能，可以快速測試對這些包含敏感雇員信息的列所做的更改。只有在試圖檢測對表中的前8列所做的更改時，COLUMNS_UpdateD()才起作用。USEpubsIFEXISTS(SelectTABLE_NAMEFROMINFORMATION_SCHEMA.TABLESWhereTABLE_NAME='employeeData')DropTABLEemployeeDataIFEXISTS(SelectTABLE_NAMEFROMINFORMATION_SCHEMA.TABLESWhereTABLE_NAME='auditEmployeeData')DropTABL

32、EauditEmployeeDataGOCreateTABLEemployeeData(emp_idintNOTNULL,emp_bankAccountNumberchar(10)NOTNULL,emp_salaryintNOTNULL,emp_SSNchar(11)NOTNULL,emp_lnamenchar(32)NOTNULL,emp_fnamenchar(32)NOTNULL,emp_managerintNOTNULL)GOCreateTABLEauditEmployeeData(audit_log_iduniqueidentifierDEFAULTNEWID(),audit_log_

33、typechar(3)NOTNULL,audit_emp_idintNOTNULL,audit_emp_bankAccountNumberchar(10)NULL,audit_emp_salaryintNULL,audit_emp_SSNchar(11)NULL,audit_usersysnameDEFAULTSUSER_SNAME(),audit_changeddatetimeDEFAULTGETDATE()GOCreateTRIGGERupdEmployeeDataONemployeeDataFORupdateAS/*Checkwhethercolumns2,3or4hasbeenupda

34、ted.Ifanyorallofcolumns2,3or4havebeenchanged,createanauditrecord.Thebitmaskis:power(2,(2-1)+power(2,(3-1)+power(2,(4-1)=14. Tocheckifallcolumns2,3,and4areupdated,use=14inplaceof>0(below).*/IF(COLUMNS_UpdateD()&14)>0/*UseIF(COLUMNS_UpdateD()&14)=14toseeifallofcolumns2,3,and4areupdated.*

35、/BEGIN-AuditOLDrecord.InsertINTOauditEmployeeData(audit_log_type,audit_emp_id,audit_emp_bankAccountNumber,audit_emp_salary,audit_emp_SSN)Select'OLD',del.emp_id,del.emp_bankAccountNumber,del.emp_salary,del.emp_SSNFROMdeleteddel-AuditNEWrecord.InsertINTOauditEmployeeData(audit_log_type,audit_emp_id,audit_emp_bankAccountNumber,audit_emp_salary,audit_emp_SSN)Select'NEW',ins.emp_id,ins.emp_bankAcc