第六章：強(qiáng)大的觸發(fā)器

1、用戶自定義函數(shù)與系統(tǒng)函數(shù)的區(qū)別用戶自定義函數(shù)的使用場合了解為什么需要觸發(fā)器理解觸發(fā)器的工作原理掌握如何使用inserted表和deleted表掌握如何創(chuàng)建： INSERT觸發(fā)器 UPDATE觸發(fā)器 DELETE觸發(fā)器q 為什么需要觸發(fā)器(TRIGGER)呢？典型的應(yīng)用就是銀行的取款機(jī)系統(tǒng) 演示： 為什么需要觸發(fā)器.sql帳戶信息表bank 交易信息表transInfo 張三取錢200 問題：沒有自動修改張三的余額最優(yōu)的解決方案就是采用觸發(fā)器：l它是一種特殊的存儲過程 l也具備事務(wù)的功能 l它能在多表之間執(zhí)行特殊的業(yè)務(wù)規(guī)則 張三開戶1000元,李四開戶1元 張三張三李四李四王五王五趙二趙二王三王

2、三宋二宋二劉五劉五插入刪除觸發(fā)器觸發(fā)趙二退休 趙二趙二員工表退休員工表q觸發(fā)器是在對表進(jìn)行插入、更新或刪除操作時(shí)自動執(zhí)行的存儲過程q觸發(fā)器通常用于強(qiáng)制業(yè)務(wù)規(guī)則q觸發(fā)器是一種高級約束，可以定義比用CHECK 約束更為復(fù)雜的約束 q可執(zhí)行復(fù)雜的SQL語句（if/while/case）q可引用其它表中的列 q觸發(fā)器定義在特定的表上，與表相關(guān)q自動觸發(fā)執(zhí)行q不能直接調(diào)用q是一個(gè)事務(wù)（可回滾）DELETE 觸發(fā)器INSERT 觸發(fā)器UPDATE 觸發(fā)器q 觸發(fā)器觸發(fā)時(shí)：q系統(tǒng)自動在內(nèi)存中創(chuàng)建deleted表或inserted表q只讀，不允許修改；觸發(fā)器執(zhí)行完成后，自動刪除q inserted 表 q臨時(shí)

3、保存了插入或更新后的記錄行 q可以從inserted表中檢查插入的數(shù)據(jù)是否滿足業(yè)務(wù)需求q 如果不滿足，則向用戶報(bào)告錯(cuò)誤消息，并回滾插入操作q deleted 表q臨時(shí)保存了刪除或更新前的記錄行 q可以從deleted表中檢查被刪除的數(shù)據(jù)是否滿足業(yè)務(wù)需求q 如果不滿足，則向用戶報(bào)告錯(cuò)誤消息，并回滾刪除操作修改操作修改操作inserted表表deleted表表增加(INSERT)記錄存放新增的記錄-刪除(DELETE)記錄-存放被刪除的記錄修改(UPDATE)記錄存放更新后的記錄存放更新前的記錄inserted表和deleted表存放的信息q創(chuàng)建觸發(fā)器的語法：CREATE TRIGGER trig

4、ger_name ON table_name WITH ENCRYPTION FOR DELETE, INSERT, UPDATE AS T-SQL語句GOWITH ENCRYPTION表示加密觸發(fā)器定義的SQL文本DELETE, INSERT, UPDATE指定觸發(fā)器的類型transInfocardIDtransType transMoney1001 00021001 0002存入 300存入 500insertedcardIDtransType transMoney1001 0001支取 200transInfocardIDtransType transMoney1001 00021001

5、 0002存入 300300存入 5005001001 0001支取 200插入記錄行觸發(fā)insert觸發(fā)器。向inserted表中插入新行的副本觸發(fā)器檢查inserted表中插入的新行數(shù)據(jù)，確定是否需要回滾或執(zhí)行其他操作qINSERT觸發(fā)器的工作原理：問題：問題：解決上述的銀行取款問題：當(dāng)向交易信息表(transInfo)中插入一條交易信息時(shí)，我們應(yīng)自動更新對應(yīng)帳戶的余額。 分析：分析：l在交易信息表上創(chuàng)建INSERT觸發(fā)器 l從inserted臨時(shí)表中獲取插入的數(shù)據(jù)行l(wèi)根據(jù)交易類型(transType)字段的值是存入/支取，l 增加/減少對應(yīng)帳戶的余額。 -關(guān)鍵代碼-CREATE TRIG

6、GER 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=myCardID ELSE UPDATE bank SE

7、T currentMoney=currentMoney+outMoney WHERE cardID=myCardID .GO 從inserted表中獲取交易類型、教員金額等根據(jù)交易類型，減少或增加對應(yīng)卡號的余額 transInfocardIDtransType transMoney存入 300存入 5001001 0001支取 200transInfocardIDtransType transMoney1001 00021001 0002存入 300存入 500deletedcardIDtransType transMoney1001 0001支取 200刪除記錄行觸發(fā)delete觸發(fā)器向de

8、leted表中插入被刪除的副本觸發(fā)器檢查deleted表中被刪除的數(shù)據(jù)，決定是否需要回滾或執(zhí)行其他操作qDELETE觸發(fā)器的工作原理：問題：問題：當(dāng)刪除交易信息表時(shí)，要求自動備份被刪除的數(shù)據(jù)到表backupTable中 。分析：分析：l在交易信息表上創(chuàng)建DELETE觸發(fā)器 l被刪除的數(shù)據(jù)可以從deleted表中獲取-關(guān)鍵代碼-CREATE TRIGGER trig_delete_transInfo ON transInfo FOR DELETE AS print 開始備份數(shù)據(jù)，請稍后. IF NOT EXISTS(SELECT * FROM sysobjects WHERE name=back

9、upTable) SELECT * INTO backupTable FROM deleted ELSE INSERT INTO backupTable SELECT * FROM deleted print 備份數(shù)據(jù)成功，備份表中的數(shù)據(jù)為: SELECT * FROM backupTable GO 從deleted表中獲取被刪除的交易記錄Deleted（更新前的數(shù)據(jù)）customerNamecardID currentMoney李四1000 0002 1bankcustomerName cardID currentMoney張三 10010001 1000 李四李四10000002 1刪除記

10、錄行向deleted表中插入被刪除的副本檢查deleted和inserted表中的數(shù)據(jù)，確定是否需要回滾或執(zhí)行其他操作qUPDATE觸發(fā)器的工作原理：李四 10000002 20001向inserted表中插入被添加的副本Inserted（更新后的數(shù)據(jù)）customerNamecardID currentMoney李四1000 0002 20001插入記錄行問題：問題：跟蹤用戶的交易，交易金額超過20000元，則取消交易，并給出錯(cuò)誤提示。 分析：分析：l在bank表上創(chuàng)建UPDATE觸發(fā)器 l修改前的數(shù)據(jù)可以從deleted表中獲取l修改后的數(shù)據(jù)可以從inserted表中獲取-關(guān)鍵代碼-CRE

11、ATE 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(afterMoney-beforeMoney)20000 BEGIN print 交易金額:+convert(varchar(8), ABS(afterMoney-beforeMoney) RAISERROR (每筆交

12、易不能超過2萬元，交易失敗,16,1) ROLLBACK TRANSACTION ENDGO 從deleted表中獲取交易前的余額，從inserted表中獲取交易后的余額交易金額是否2萬回滾事務(wù)，撤銷交易qUPDATE觸發(fā)器除了跟蹤數(shù)據(jù)的變化（修改）外，還可以檢查是否修改了某列的數(shù)據(jù) q使用UPDATE（列）函數(shù)檢測是否修改了某列問題：問題：交易日期一般由系統(tǒng)自動產(chǎn)生，默認(rèn)為當(dāng)前日期。為了安全起見，一般禁止修改，以防舞弊。 分析：分析：UPDATE（列名）函數(shù)可以檢測是否修改了某列 -關(guān)鍵代碼-CREATE TRIGGER trig_update_transInfo ON transInfo

13、FOR UPDATE AS IF UPDATE(transDate) BEGIN print 交易失敗. RAISERROR (安全警告：交易日期不能修改， 由系統(tǒng)自動產(chǎn)生,16,1) ROLLBACK TRANSACTION ENDGO 檢查是否修改了交易日期列transDate回滾事務(wù)，撤銷交易SQL Server 2005增加功能之一就是數(shù)據(jù)定義語言 (DDL) 觸發(fā)器。在SQL Server 7.0和2000中，我們使用數(shù)據(jù)操作語言 (DML) 觸發(fā)器，當(dāng)INSERT,UPDATE或者DELETE語句被執(zhí)行的時(shí)候執(zhí)行一段SQL語句或存儲過程，它們只能用于表或視圖。 在SQL Serve

14、r 2005中，我們可以在DDL語句上建立一個(gè)觸發(fā)器來執(zhí)行任何操作。觸發(fā)器的作用范圍可以是數(shù)據(jù)庫層或是服務(wù)器層。DDL觸發(fā)器常用在數(shù)據(jù)庫結(jié)構(gòu)要變更的時(shí)候你期望的某個(gè)動作被執(zhí)行。讓我們在數(shù)據(jù)庫上建立一個(gè)觸發(fā)器阻止任何用戶Create,Alter和Drop表或存儲過程。我們假定我們有一個(gè)數(shù)據(jù)庫叫DDLTrTest，這個(gè)數(shù)據(jù)庫中有一個(gè)表(MyTable)和一個(gè)存儲過程(Usp_QueryMyTable)。示例示例4-1q 觸發(fā)器是在對表進(jìn)行插入、更新或刪除操作時(shí)自動執(zhí)行的存儲過程，觸發(fā)器通常用于強(qiáng)制業(yè)務(wù)規(guī)則q 觸發(fā)器還是一個(gè)特殊的事務(wù)單元，當(dāng)出現(xiàn)錯(cuò)誤時(shí)，可以執(zhí)行ROLLBACK TRANSACTIO

15、N回滾撤銷操作 q 觸發(fā)器一般都需要使用臨時(shí)表：deleted表和inserted表，它們存放了被刪除或插入的記錄行副本 q 觸發(fā)器類型：q INSERT觸發(fā)器q UPDATE觸發(fā)器q DELETE觸發(fā)器1、觸發(fā)器的作用？ 觸發(fā)器是一中特殊的存儲過程，主要是通過事件來觸發(fā)而被執(zhí)行的。它觸發(fā)器是一中特殊的存儲過程，主要是通過事件來觸發(fā)而被執(zhí)行的。它可以強(qiáng)化約束，來維護(hù)數(shù)據(jù)的完整性和一致性，可以跟蹤數(shù)據(jù)庫內(nèi)的操可以強(qiáng)化約束，來維護(hù)數(shù)據(jù)的完整性和一致性，可以跟蹤數(shù)據(jù)庫內(nèi)的操作從而不允許未經(jīng)許可的更新和變化?？梢月?lián)級運(yùn)算。如，某表上的觸作從而不允許未經(jīng)許可的更新和變化?？梢月?lián)級運(yùn)算。如，某表上的觸發(fā)器上包含對另一個(gè)表的數(shù)據(jù)操作，而該操作又會導(dǎo)致該表觸發(fā)器被觸發(fā)器上包含對另一個(gè)表的數(shù)據(jù)操作，而該操作又會導(dǎo)致該表觸發(fā)器被觸發(fā)。發(fā)。 2、維護(hù)數(shù)據(jù)庫的完整性和一致性，你喜歡用觸發(fā)器還是自寫業(yè)務(wù)邏輯？為