任務(wù)六觸發(fā)器的規(guī)劃與設(shè)計

一、任務(wù)目標

1掌握創(chuàng)建和使用觸發(fā)器來實現(xiàn)數(shù)據(jù)有效性和完整性；2掌握創(chuàng)建和使用觸發(fā)器來實現(xiàn)數(shù)據(jù)的業(yè)務(wù)邏輯；3掌握創(chuàng)建和使用觸發(fā)器來實現(xiàn)數(shù)據(jù)的保護；二、教學(xué)任務(wù)

1介紹觸發(fā)器的概念；2介紹觸發(fā)器的觸發(fā)機制；3創(chuàng)建帶有提示信息的觸發(fā)器；4創(chuàng)建限制取值范圍約束的觸發(fā)器；5創(chuàng)建實現(xiàn)用戶邏輯上數(shù)據(jù)完整性觸發(fā)器；6分析設(shè)計分銷系統(tǒng)中需要的觸發(fā)器6.1.1觸發(fā)器的概念

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

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

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

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

CREATETRIGGERtrigger_nameONtable_name[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{[INSERT][,][DELETE][,][UPDATE]｝ASSQL_statement[,…n]6.1.3觸發(fā)器的創(chuàng)建

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

任務(wù)6-2：創(chuàng)建帶有提示信息的觸發(fā)器。當用戶在期初庫存表中插入數(shù)據(jù)時，產(chǎn)生一條提示信息。在SQLQuery窗口中執(zhí)行如下命令：CreateTRIGGERTRI_insert_qckcon期初庫存FORINSERTASPRINT'在期初庫存表中插入了數(shù)據(jù)！'6.1.4觸發(fā)器的實例

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

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

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

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

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

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

任務(wù)6-10：創(chuàng)建用于保護“分銷系統(tǒng)”數(shù)據(jù)庫中的數(shù)據(jù)表不被刪除的觸發(fā)發(fā)器。在SQLQuery窗口中執(zhí)行如下命令：createtriggerdisable_table_droppingondatabasefordrop_tableasbeginraiserror('分銷系統(tǒng)數(shù)據(jù)表不能被刪除',16,10)rollbackend6.2分銷系統(tǒng)觸發(fā)器的設(shè)計

6.2.1分銷系統(tǒng)觸發(fā)器規(guī)劃

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

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

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

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

在SQLQuery窗口中執(zhí)行如下命令：createtriggerTRI_xsddmxbon銷售訂單明細表forinsert,update,deleteasbeginUpdate銷售訂單明細表set金額=isnull(單價,0)*(isnull(數(shù)量,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分銷系統(tǒng)觸發(fā)器設(shè)計

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

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