任務六觸發器的規劃與設計

一、任務目標

1掌握創建和使用觸發器來實現數據有效性和完整性；2掌握創建和使用觸發器來實現數據的業務邏輯；3掌握創建和使用觸發器來實現數據的保護；二、教學任務

1介紹觸發器的概念；2介紹觸發器的觸發機制；3創建帶有提示信息的觸發器；4創建限制取值范圍約束的觸發器；5創建實現用戶邏輯上數據完整性觸發器；6分析設計分銷系統中需要的觸發器6.1.1觸發器的概念

觸發器是一種特殊的存儲過程，其特殊性在于它不需要由用戶調用執行，而是當用戶對表中的數據進行UPDATE、INSERT或DELETE操作時自動觸發執行。觸發器通常用于保證業務規則和數據完整性約束，其優點是用戶可以用編程的方法來實現復雜的處理邏輯和業務規則，增強了數據完整性約束的功能。在SQLServer2005中，觸發器有了更進一步的功能，在數據表（庫）發生Create、Alter和Drop操作時，也會自動激活執行。6.1.2觸發器的分類

在SQLServer2005中，觸發器可以分為兩大類：DML觸發器和DDL觸發器。DML觸發器：DML觸發器是當數據庫服務器中發生數據操作語言（DataManipulationLanguage）事件時執行的存儲過程。DML事件包括在指定表或視圖中修改數據的INSERT語句、UPDATE語句或DELETE語句。DML觸發器又分為兩類：After觸發器和InsteadOf觸發器。DML觸發器可以查詢其他表，還可以包含復雜的T-SQL語句。系統將觸發器和觸發它的語句作為可在觸發器內回滾的單個事務對待，如果檢測到錯誤（例如，磁盤空間不足），則整個事務即自動回滾。DDL觸發器：DDL觸發器是當數據庫服務器中發生數據定義語言（DataDefinitionLanguage）事件時執行的存儲過程。這是SQLServer2005的新增功能。它與DML觸發器不同的是，它不會為響應針對表或視圖的UPDATE、INSERT或DELETE語句而激發，相反，它會為響應多種數據定義語言（DDL）語句而激發。這些語句主要是以CREATE、ALTER和DROP開頭的語句。DDL觸發器一般用于執行數據庫中管理任務。如審核和規范數據庫操作、防止數據庫表結構被修改等。DML觸發器的分類SQLServer2005的DML觸發器分為兩類：After觸發器和InsteadOf觸發器。After觸發器：這類觸發器是在記錄已經改變完之后（after），才會被激活執行，它主要是用于記錄變更后的處理或檢查，一旦發現錯誤，也可以用RollbackTransaction語句來回滾本次的操作。InsteadOf觸發器：這類觸發器一般是用來取代原本的操作，在記錄變更之前發生的，它并不去執行原來SQL語句里的操作（Insert、Update、Delete），而去執行觸發器本身所定義的操作。6.1.2觸發器的分類

DML觸發器的工作原理在DML觸發器的工作過程中，SQLServer建立和管理兩個臨時的虛擬表，一個是Inserted（插入）表，一個是Deleted（刪除）表。這兩個表是建在數據庫服務器的內存中的，是由系統管理的邏輯表，而不是真正存儲在數據庫中的物理表。這兩個特殊表可供用戶讀取，但是用戶不能直接修改表中的數據。Inserted和Deleted兩個表的結構與觸發器所在數據表的結構是完全一致的，當觸發器的工作完成之后，這兩個表也將會從內存中刪除。6.1.2觸發器的分類

激活觸發器的SQL語句Inserted表Deleted表INSERT所要添加的行空UPDATE新的行舊的行DELETE空刪除的行6.1.3觸發器的創建

CREATETRIGGERtrigger_nameONtable_name[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{[INSERT][,][DELETE][,][UPDATE]｝ASSQL_statement[,…n]6.1.3觸發器的創建

參數含義說明如下：CREATETRIGGER：用來觸發器。trigger_name：觸發器名稱，觸發器是對象，必須具有數據庫中的唯一名稱。ONtable_name：用于指定觸發執行觸發器的表。WithEncryption：用來加密觸發器。如果使用了這個參數，該觸發器將會被加密，任何人都看不到觸發器的內容了。AFTER：指定觸發器只有在引發觸發器執行的SQL語句指定的操作都已經成功執行，并且所有的約束檢查也成功完成后，才執行此觸發器。這種類型的觸發器稱為后觸發型觸發器。FOR：如果只是指定FOR關鍵字，則AFTER為默認值。INSTEADOF：指定執行觸發器而不是執行引發觸發器執行的SQL語句，從而替代觸發語句的操作。這種觸發器稱為前觸發型觸發器，一個表只能定義一個INSTEADOF觸發器。INSERT,DELETE,UPDATE：指定引發觸發器執行的操作，若同時指定多個操作，則各操作之間用逗號分隔。6.1.4觸發器的實例

任務6-2：創建帶有提示信息的觸發器。當用戶在期初庫存表中插入數據時，產生一條提示信息。在SQLQuery窗口中執行如下命令：CreateTRIGGERTRI_insert_qckcon期初庫存FORINSERTASPRINT'在期初庫存表中插入了數據！'6.1.4觸發器的實例

任務6-3：創建限制取值范圍約束的觸發器。限制期初庫存表中的期初數量和期初單價必須是大于零的數字。在SQLQuery窗口中執行如下命令：CreateTRIGGERTRI_check_qckcON期初庫存FORINSERT,UPDATEASIfexists(select*frominsertedwhereisnull(期初數量,0)<0orisnull(期初單價,0)<0)BeginPRINT'期初數量或期初單價不能為負數，更改失敗！'RollbackEnd6.1.4觸發器的實例

任務6-4：為收款單創建一個insteadof觸發器，使得新插入記錄中收款金額小于應收總額時，備注內容自動填寫為“未收訖”，而收款金額等于應收總額時，備注內容自動填寫為“已收訖”。在SQLQuery窗口中執行如下命令：CreateTRIGGERTRI_SKD_BZON收款單INSTEADOFINSERTASbegininsertinto收款單select*frominsertedupdate收款單set備注='未收訖'where收款金額<應收總額and(收款單號in(select收款單號frominserted))update收款單set備注='已收訖'where收款金額=應收總額and(收款單號in(select收款單號frominserted))end6.1.5查看、修改和刪除觸發器

ALTERTRIGGERtrigger_nameONtable_name[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{[INSERT][,][DELETE][,][UPDATE]｝ASSQL_statement[,…n]6.1.5查看、修改和刪除觸發器

任務6-8：修改觸發器TRI_check_cgmx的定義，將采購訂單明細表的數量字段的上限修改為1200。在SQLQuery窗口中執行如下命令：ALTERTRIGGERTRI_check_cgmxON采購訂單明細表FORINSERT,UPDATEASIf(select數量frominserted)>1200BeginPRINT'采購數量超出上限，操作失敗！'Rollbackend6.1.5查看、修改和刪除觸發器

可以使用DropTrigger語句來刪除觸發器。任務6-9：刪除觸發器TRI_insert_qckc。在SQLQuery窗口中執行如下命令：droptriggerTRI_insert_qckc該語句執行后，觸發器TRI_insert_qckc即被刪除。6.1.6DDL觸發器

創建DDL觸發器的語法如下：CREATETRIGGERtrigger_nameON{ALLSERVER|DATABASE}[WITH<ddl_trigger_option>[,...n]]{FOR|AFTER}{event_type|event_group}[,...n]AS{sql_statement[;][...n]|EXTERNALNAME<methodspecifier>[;]}參數含義說明如下：trigger_name：觸發器名稱，必須遵循標識符規則。ONAllServer：是將DDL觸發器作用到整個當前的服務器上。如果指定了這個參數，在當前服務器上的任何一個數據庫都能激活該觸發器。ONDatabase：是將DDL觸發器作用到當前數據庫，只能在這個數據庫上激活該觸發器。For或After：是同一個意思，指定的是After觸發器，DDL觸發器無法作為InsteadOf觸發器。event_type：執行之后將導致激發DDL觸發器的Transact-SQL語言事件的名稱。event_group：預定義的Transact-SQL語言事件分組的名稱。6.1.6DDL觸發器

任務6-10：創建用于保護“分銷系統”數據庫中的數據表不被刪除的觸發發器。在SQLQuery窗口中執行如下命令：createtriggerdisable_table_droppingondatabasefordrop_tableasbeginraiserror('分銷系統數據表不能被刪除',16,10)rollbackend6.2分銷系統觸發器的設計

6.2.1分銷系統觸發器規劃

在分銷系統的數據表中，銷售訂單明細表有字段[數量]、[單價]、[金額]。根據業務邏輯，金額＝單價×數量，所以對于銷售訂單明細表應該創建一個觸發器，使得[金額]的值是由[單價]和[數量]的乘積得來的，保證該業務邏輯的準確性。同樣地，根據業務邏輯，在某個銷售訂單中，銷售訂單明細表中字段[金額]和銷售訂單中字段[總金額]存在這樣的關系：總金額等于該銷售訂單明細中的金額的總和。為此，還需要為銷售訂單明細表創建一個觸發器來保證該業務邏輯。類似地，在采購訂單與采購訂單明細表之間、盤點表與盤點表明細表之間、入庫單與入庫單明細表之間、出庫單與出庫單明細表之間、期初庫存，也有相同的業務邏輯，都需要建立保證相關業務邏輯準確性的觸發器。在部分缺少外鍵約束的數據表中，比如入庫單明細表，該表的[倉庫編碼]字段的值必須是倉庫資料表中存在的倉庫編碼，因為倉庫資料表中不存在的倉庫編碼就意味著這樣的倉庫編碼所標識的倉庫是不存在的，現實中是絕不可能把商品入庫到一個不存在的倉庫中的。同樣地，該表的[倉位編碼]字段的值必須是倉位資料表中存在的數據。要保證上述數據完整性要求，可以創建相應的觸發器。在分銷系統的數據表中，雖然不少表都定義了外鍵約束，但并沒有定義級聯刪除和級聯修改，為此，可以定義相應的觸發器來達到相應的效果。比如，若某個銷售訂單被刪除，則該銷售訂單對應的明細記錄也能被自動刪除。6.2.2分銷系統觸發器設計

任務6-11：為表期初庫存創建一個觸發器，保證插入新記錄、更改了期初數量或更改了期初單價后期初金額都會隨著變化而自動更新為期初數量和期初單價的乘積。對此觸發器可以做如下分析：如果【期初單價】或者【期初數量】字段有更改，則將期初庫存中序號跟inserted表中序號對應的記錄的期初金額按公式【期初金額】=【期初單價】*【期初數量】計算后進行更新。6.2.2分銷系統觸發器設計

在SQLQuery窗口中執行如下命令：createtriggerTRI_update_qckcon期初庫存forinsert,updateasifupdate(期初單價)orupdate(期初數量)Update期初庫存set期初金額=isnull(期初單價,0)*(isnull(期初數量,0))where序號in(select序號frominserted)6.2.2分銷系統觸發器設計

任務6-12：為銷售訂單明細表創建一個觸發器，無論該表的新增、修改或刪除記錄都能保證記錄中的金額為數量和單價的乘積，同時其相應的銷售訂單主表中的總金額也能保持準確。對此觸發器可以做如下分析：先在銷售訂單明細表中更新Inserted表中涉及的記錄的金額字段，然后在銷售訂單表中更新Inserted表中涉及的銷售訂單號的總金額字段，還要注意兼顧刪除記錄的情況，在銷售訂單表中更新Deleted表中涉及的銷售訂單號的總金額字段。6.2.2分銷系統觸發器設計

在SQLQuery窗口中執行如下命令：createtriggerTRI_xsddmxbon銷售訂單明細表forinsert,update,deleteasbeginUpdate銷售訂單明細表set金額=isnull(單價,0)*(isnull(數量,0))where銷售訂單號in(select銷售訂單號frominserted)and序號in(select序號frominsertedwhere銷售訂單號=銷售訂單明細表.銷售訂單號)update銷售訂單set總金額=(selectsum(金額)from銷售訂單明細表where銷售訂單號in(select銷售訂單號frominserted))where銷售訂單號in(select銷售訂單號frominserted)update銷售訂單set總金額=isnull((selectsum(金額)from銷售訂單明細表where銷售訂單號in(select銷售訂單號fromdeleted)),0)where銷售訂單號in(select銷售訂單號fromdeleted)end6.2.2分銷系統觸發器設計

任務6-13：為入庫單明細表創建一個觸發器，保證插入的新記錄或修改的記錄中，倉庫編碼和倉位編碼字段內容都是已存在于倉位資料表的合法數據。對此觸發器可以做如下分析：由于倉庫編碼和倉位編碼在倉位資料表中具有組合唯一性，若在Inserted表中查詢到的“倉庫編碼+倉位編碼”在倉位資料表中并不存在，則要插入的或更新的記錄中的倉庫編碼、倉位編碼不是合法數據，這時候需要回滾處理。6.2.2分銷系統觸發器設計

在SQLQuery窗口中執行如下命令：createtriggerTRI_rkdmxbon入庫單明細表forinsert,updateasbegindeclare@iintset@i=(selectcount(倉庫編碼+倉位編碼)frominsertedwhere倉庫編碼+倉位編碼notin(selectdistinct倉庫編碼+倉位編碼from倉位資料))if@i>0beginprint'非法倉庫倉位數據！'