SQL Server 2000編程之觸發(fā)器

1、SQL Server 2000編程觸發(fā)器一、 了解觸發(fā)器-創(chuàng)建帳戶信息表bank和交易表transInfoCREATE TABLE bank -帳戶信息表( customerName CHAR(8) NOT NULL, -顧客姓名 cardID CHAR(10) NOT NULL , -卡號 currentMoney MONEY NOT NULL -當(dāng)前余額)CREATE TABLE transInfo -交易信息表( cardID CHAR(10) NOT NULL, -卡號 transType CHAR(4) NOT NULL, -交易類型（存入/支取） transMoney MONEY

2、NOT NULL, -交易金額 transDate DATETIME NOT NULL, -交易日期)一個典型的應(yīng)用：銀行的取款系統(tǒng) /*-添加約束：帳戶余額不能少于元，交易日期默認(rèn)為當(dāng)天日期-*/ALTER TABLE bank ADD CONSTRAINT CK_currentMoney CHECK(currentMoney=1)ALTER TABLE transInfo ADD CONSTRAINT DF_transDate DEFAULT(getDate( ) FOR transDateGO/*-插入測試數(shù)據(jù)：張三開戶，開戶金額為；李四開戶，開戶金額-*/INSERT INTO ban

3、k(customerName,cardID,currentMoney) VALUES(張三,1001 0001,1000)INSERT INTO bank(customerName,cardID,currentMoney) VALUES(李四,1001 0002,1)/*-插入測試數(shù)據(jù)：張三取錢-*/INSERT INTO transInfo(cardID,transType,transMoney) VALUES(1001 0001,支取,200)-查看結(jié)果SELECT * FROM bankSELECT * FROM transInfo一個典型的應(yīng)用：銀行的取款系統(tǒng) 帳戶信息表bank 交易

4、信息表transInfo 張三取錢200 問題：沒有自動修改張三的余額最優(yōu)的解決方案就是采用觸發(fā)器：它是一種特殊的存儲過程 也具備事務(wù)的功能 它能在多表之間執(zhí)行特殊的業(yè)務(wù)規(guī)則 張三開戶1000元,李四開戶1元 張三李四王五趙二王三宋二劉五插入1.什么是觸發(fā)器刪除觸發(fā)器觸發(fā)趙二退休 趙二員工表退休員工表觸發(fā)器是在對表進(jìn)行插入、更新或刪除操作時自動執(zhí)行的存儲過程觸發(fā)器通常用于強(qiáng)制業(yè)務(wù)規(guī)則觸發(fā)器是一種高級約束，可以定義比用CHECK 約束更為復(fù)雜的約束 可執(zhí)行復(fù)雜的SQL語句（if/while/case）可引用其它表中的列 什么是觸發(fā)器2.觸發(fā)器主要提供的功能在數(shù)據(jù)庫中的相關(guān)表上實現(xiàn)級聯(lián)更改事務(wù)功能

5、，撤銷或回滾違反引用完整性的操作，防止非法修改操作執(zhí)行比 CHECK 約束更復(fù)雜的約束操作比較數(shù)據(jù)修改前后的狀態(tài)大部分觸發(fā)器提供了引用被修改數(shù)據(jù)的能力，這樣就允許用戶在觸發(fā)器中引用正被修改語句所影響的行在一張表的同一類型操作（update,insert,delete）上設(shè)置多個觸發(fā)器，從而可以對同樣的修改語句執(zhí)行不同的多種操作。3.使用觸發(fā)器的說明和特定表關(guān)聯(lián)，自動調(diào)用。當(dāng)試圖在某個表插入、更新或刪除數(shù)據(jù)，而在那個表上定義了針對所做動作的觸發(fā)器，那么觸發(fā)器會自動執(zhí)行。只有表的擁有者才可以在表上創(chuàng)建或刪除觸發(fā)器，這種權(quán)限不許轉(zhuǎn)授。不能再在視圖或臨時表上創(chuàng)建觸發(fā)器，但可以在觸發(fā)器中引用視圖或臨時表

6、。是一個事務(wù)的部分。如果觸發(fā)器執(zhí)行不成功，則整個修改事務(wù)回滾。不像普通的存儲過程，觸發(fā)器不能被直接調(diào)用，也不傳遞或接受參數(shù)當(dāng)使用約束、規(guī)則、默認(rèn)值就可以實現(xiàn)預(yù)定的數(shù)據(jù)完整性時，應(yīng)該優(yōu)先使用前3種措施。4.觸發(fā)器的類型DELETE 觸發(fā)器INSERT 觸發(fā)器UPDATE 觸發(fā)器觸發(fā)器觸發(fā)時：系統(tǒng)自動在內(nèi)存中創(chuàng)建deleted表或inserted表只讀，不允許修改；觸發(fā)器執(zhí)行完成后，自動刪除inserted 表 臨時保存了插入或更新后的記錄行 可以從inserted表中檢查插入的數(shù)據(jù)是否滿足業(yè)務(wù)需求如果不滿足，則向用戶報告錯誤消息，并回滾操作deleted 表臨時保存了刪除或更新前的記錄行 可以從

7、deleted表中檢查被刪除的數(shù)據(jù)是否滿足業(yè)務(wù)需求如果不滿足，則向用戶報告錯誤消息，并回滾操作5.inserted 和deleted inserted 和deleted 表修改操作inserted表deleted表增加(INSERT)記錄存放新增的記錄-刪除(DELETE)記錄-存放被刪除的記錄修改(UPDATE)記錄存放更新后的記錄存放更新前的記錄inserted表和deleted表存放的信息創(chuàng)建觸發(fā)器的語法： 二、創(chuàng)建觸發(fā)器CREATE TRIGGER trigger_name ON table_name WITH ENCRYPTION FOR/AFTER/INSTEAD OF DELET

8、E, INSERT, UPDATE AS T-SQL語句GOWITH ENCRYPTION表示加密觸發(fā)器定義的SQL文本DELETE, INSERT, UPDATE指定觸發(fā)器的類型創(chuàng)建觸發(fā)器FOR 和 AFTER 是完全相等的，創(chuàng)建相同類型的觸發(fā)器，在INSERT、UPDATE 或 DELETE 語句執(zhí)行后觸發(fā)INSTEAD OF 觸發(fā)器取消觸發(fā)動作，執(zhí)行替代操作創(chuàng)建觸發(fā)器后，其信息插入 sysobjects 和 syscomments 系統(tǒng)表中SQL Server 不允許在觸發(fā)器中使用下列語句：ALTER DATABASE、CREATE DATABASE、DISK INIT、DISK RES

9、IZE、DROP DATABASE、LOAD DATABASE、LOAD LOG、RECONFIGURE、RESTORE DATABASE、RESTORE LOG1. INSERT觸發(fā)器transInfocardIDtransType transMoney1001 00021001 0002存入 300存入 500insertedcardIDtransType transMoney1001 0001 支取 200transInfocardIDtransType transMoney1001 00021001 0002存入 300存入 5001001 0001支取 200插入記錄行觸發(fā)inser

10、t觸發(fā)器。向inserted表中插入新行的副本觸發(fā)器檢查inserted表中插入的新行數(shù)據(jù)，確定是否需要回滾或執(zhí)行其他操作INSERT觸發(fā)器的工作原理：INSERT 觸發(fā)器示例問題：解決上述的銀行取款問題：當(dāng)向交易信息表(transInfo)中插入一條交易信息時，我們應(yīng)自動更新對應(yīng)帳戶的余額。帳戶信息表bank 交易信息表transInfo 張三取錢200 問題：沒有自動修改張三的余額張三開戶1000元,李四開戶1元 分析：在交易信息表上創(chuàng)建INSERT觸發(fā)器 從inserted臨時表中獲取插入的數(shù)據(jù)行根據(jù)交易類型(transType)字段的值是存入/支取， 增加/減少對應(yīng)帳戶的余額。INSE

11、RT 觸發(fā)器示例-關(guān)鍵代碼-CREATE TRIGGER trig_transInfo ON transInfo FOR INSERT AS DECLARE type char(4),outMoney MONEY DECLARE myCardID char(10),balance MONEY SELECT type=transType,outMoney=transMoney， myCardID=cardID FROM inserted IF (type=支取) UPDATE bank SET currentMoney=currentMoney- outMoney WHERE cardID=my

12、CardID ELSE UPDATE bank SET currentMoney = currentMoney + outMoney WHERE cardID=myCardID .GO 從inserted表中獲取交易類型、交易金額等根據(jù)交易類型，減少或增加對應(yīng)卡號的余額 INSERT 觸發(fā)器示例transInfocardIDtransType transMoney1001 00021001 0002存入 300存入 5001001 0001支取 2002. DELETE觸發(fā)器transInfocardIDtransType transMoney1001 00021001 0002存入 300存

13、入 500deletedcardIDtransType transMoney1001 0001支取 200刪除記錄行觸發(fā)delete觸發(fā)器向deleted表中插入被刪除的副本觸發(fā)器檢查deleted表中被刪除的數(shù)據(jù)，決定是否需要回滾或執(zhí)行其他操作DELETE觸發(fā)器的工作原理：問題：當(dāng)刪除交易信息表時，要求自動備份被刪除的數(shù)據(jù)到表backupTable中 。分析：在交易信息表上創(chuàng)建DELETE觸發(fā)器 被刪除的數(shù)據(jù)可以從deleted表中獲取DELETE觸發(fā)器示例從deleted表中獲取被刪除的交易記錄DELETE觸發(fā)器示例-關(guān)鍵代碼-CREATE TRIGGER trig_delete_tran

14、sInfo ON transInfo FOR DELETE AS print 開始備份數(shù)據(jù)，請稍后. IF NOT EXISTS(SELECT * FROM sysobjects WHERE name=backupTable) SELECT * INTO backupTable FROM deleted ELSE INSERT INTO backupTable SELECT * FROM deleted print 備份數(shù)據(jù)成功，備份表中的數(shù)據(jù)為: SELECT * FROM backupTable GODELETE觸發(fā)器示例Deleted（更新前的數(shù)據(jù)）customerNamecardID

15、currentMoney李四1000 0002 1bankcustomerName cardID currentMoney張三 10010001 1000 李四10000002 13. UPDATE觸發(fā)器刪除記錄行向deleted表中插入被刪除的副本檢查deleted和inserted表中的數(shù)據(jù)，確定是否需要回滾或執(zhí)行其他操作UPDATE觸發(fā)器的工作原理：李四 10000002 20001向inserted表中插入被添加的副本Inserted（更新后的數(shù)據(jù)）customerNamecardID currentMoney李四1000 0002 20001插入記錄行問題：跟蹤用戶的交易，交易金額超

16、過20000元，則取消交易，并給出錯誤提示。 分析：在bank表上創(chuàng)建UPDATE觸發(fā)器 修改前的數(shù)據(jù)可以從deleted表中獲取修改后的數(shù)據(jù)可以從inserted表中獲取UPDATE觸發(fā)器示例CREATE TRIGGER trig_update_bank ON bank FOR UPDATE AS DECLARE beforeMoney MONEY,afterMoney MONEY SELECT beforeMoney=currentMoney FROM deleted SELECT afterMoney=currentMoney FROM inserted IF ABS(afterMone

17、y-beforeMoney)20000 BEGIN print 交易金額:+convert(varchar(8), ABS(afterMoney-beforeMoney) RAISERROR (每筆交易不能超過2萬元，交易失敗,16,1) ROLLBACK TRANSACTION ENDGO從deleted表中獲取交易前的余額，從inserted表中獲取交易后的余額交易金額是否2萬回滾事務(wù)，撤銷交易UPDATE觸發(fā)器-關(guān)鍵代碼-UPDATE觸發(fā)器列級 UPDATE 觸發(fā)器UPDATE觸發(fā)器除了跟蹤數(shù)據(jù)的變化（修改）外，還可以檢查是否修改了某列的數(shù)據(jù) 使用UPDATE（列）函數(shù)檢測是否修改了某列

18、問題：交易日期一般由系統(tǒng)自動產(chǎn)生，默認(rèn)為當(dāng)前日期。為了安全起見，一般禁止修改，以防舞弊。 分析：UPDATE（列名）函數(shù)可以檢測是否修改了某列 -關(guān)鍵代碼-CREATE TRIGGER trig_update_transInfo ON transInfo FOR UPDATE AS IF UPDATE(transDate) BEGIN print 交易失敗. RAISERROR (安全警告：交易日期不能修改， 由系統(tǒng)自動產(chǎn)生,16,1) ROLLBACK TRANSACTION ENDGO 檢查是否修改了交易日期列transDate回滾事務(wù)，撤銷交易列級 UPDATE 觸發(fā)器列級 UPDATE

19、 觸發(fā)器4. INSTEAD OF 觸發(fā)器的工作過程INSTEAD OF 觸發(fā)器可以在表和視圖上定義 INSTEAD OF 觸發(fā)器。INSTEAD OF 觸發(fā)器代替原觸發(fā)動作執(zhí)行，增加了視圖上所能進(jìn)行的更新的種類每個表上對每個觸發(fā)動作（INSERT、UPDATE 或 DELETE）只能定義一個 INSTEAD OF 觸發(fā)器不能在具有 WITH CHECK OPTION 選項的視圖上創(chuàng)建 INSTEAD OF 觸發(fā)器INSTEAD OF 觸發(fā)器可使一般不支持更新的視圖可以被更新截獲對視圖的操作，將其重導(dǎo)向底層表在 INSTEAD OF DELETE 觸發(fā)器中，通過 deleted 表訪問欲刪除的

20、行；在 INSTEAD OF UPDATE 或 INSTEAD OF INSERT 觸發(fā)器中，通過 inserted 表訪問新增加的行INSTEAD OF 觸發(fā)器的工作過程（續(xù)）創(chuàng)建結(jié)合兩個或多個表的視圖CREATE VIEW vw_Customers ASSELECT * FROM CustomersMexUNIONSELECT * FROM CustomersGerCustomersMexCustomerIDCompanyNameCountryPhoneANATRANTONCENTCAna TrujillAntonio MCentro CoMexicoMexicoMexico(5) 555

21、-4729(5) 555-3932(5) 555-3392CustomersCustomerIDCompanyNameCountryPhoneALFKIANATRANTONAlfreds FuAna TrujillAntonio MGermanyMexicoMexic5) 555-4729(5) 555-3932CustomersGerCustomerIDCompanyNameCountryPhoneALFKIBLAUSDRACDAlfreds FuBlauer SeDrachenbGermanyGermanyGermany030-00743210621-084600

22、241-039123-創(chuàng)建觸發(fā)器，將對視圖的插入引導(dǎo)到對基本表的插入CREATE TRIGGER tr_Customers ON vw_ CustomersINSTEAD OF insertASBEGIN insert into customersger (customerid,companyname,country,phone) SELECT i.customerid,panyname,i.country,i.phone FROM INSERTED i ENDINSTEAD OF 觸發(fā)器的工作過程（續(xù)）CustomersMexCustomerIDCompanyNameCountryPhone

23、ANATRANTONCENTCAna TrujillAntonio MCentro CoMexicoMexicoMexico(5) 555-4729(5) 555-3932(5) 555-3392CustomersGerCustomerIDCompanyNameCountryPhoneALFKIBLAUSDRACDAlfreds FuBlauer SeDrachenbGermanyGermanyGermany030-00743210621-084600241-039123INSTEAD OF 觸發(fā)器將插入導(dǎo)向基表CustomersCustomerIDCompanyNameCountryPhon

24、eALFKIANATRANTONAlfreds FuAna TrujillAntonio MGermanyMexicoMexic5) 555-4729(5) 555-3932Insert into -對視圖執(zhí)行 insert 操作vw_customers(customerid,comanyname,country,phone)Values(ALFKI,Alfreds Fu,GermanyALFKI Alfreds FuGermanLFKIAlfreds FuGerman、更改和刪除觸發(fā)器更改

25、觸發(fā)器語法：ALTER TRIGGER 觸發(fā)器名 使用新的定義代替觸發(fā)器原有的定義，觸發(fā)動作也可以更改由于延遲名稱解析，觸發(fā)器可以引用觸發(fā)器定義時不存在的表，但是創(chuàng)建觸發(fā)器時會收到一條警告信息禁用或啟用觸發(fā)器語法：ALTER TABLE 表名 ENABLE | DISABLE TRIGGER ALL | 觸發(fā)器名 , n 更改和刪除觸發(fā)器（續(xù)）刪除觸發(fā)器語法：DROP TRIGGER 觸發(fā)器名若關(guān)聯(lián)表被刪除，則觸發(fā)器自動刪除觸發(fā)器是在對表進(jìn)行插入、更新或刪除操作時自動執(zhí)行的存儲過程，觸發(fā)器通常用于強(qiáng)制業(yè)務(wù)規(guī)則觸發(fā)器還是一個特殊的事務(wù)單元，當(dāng)出現(xiàn)錯誤時，可以執(zhí)行ROLLBACK TRANSACTION回滾撤銷操作 觸發(fā)器一般都需要使用臨時表：deleted表和inserted表，它們存放了被刪除或插入的記錄行副本 觸發(fā)器類型： INSERT觸發(fā)器 UPDATE觸發(fā)器 DELETE觸發(fā)器 INSTEAD OF總結(jié)使用觸發(fā)器的考慮事項大部分觸發(fā)器在動作后執(zhí)行，約束和 INSTEAD OF 觸發(fā)器是在動作前執(zhí)行的大部分觸發(fā)器在所定義的表上執(zhí)行 INSERT、