1、第十章 觸發(fā)器SQL SERVER 2005課程2回顧 2-1 代碼閱讀，下列系統(tǒng)存儲(chǔ)過程的功能是： EXEC sp_helpconstraint stuInfo EXEC sp_helpindex stuMarks EXEC sp_renamedb stuDB,studentDB EXEC xp_cmdshell mkdir d:project 存儲(chǔ)過程的參數(shù)分為哪兩類？ 根據(jù)您的理解，print語句和RAISERROR語句有什么區(qū)別3回顧 2-2 代碼閱讀代碼閱讀:指出下列語句的錯(cuò)誤指出下列語句的錯(cuò)誤 CREATE PROCEDURE proc_stuInfo -定義存儲(chǔ)過程 stuNam

2、e VARCHAR(10), m INT OUTPUT, stuAge=18 INT AS .代碼略 GO -調(diào)用存儲(chǔ)過程 DECLARE s INT EXEC proc_stuInfo 張三,s,30 EXEC proc_stuInfo 張三,s EXEC proc_stuInfo stuAge=22,stuName=李四,m=s OUTPUTstuAge INT=18 EXEC proc_stuInfo 張三,s OUTPUT,30EXEC proc_stuInfo 張三,s OUTPUT4本章目標(biāo) 了解為什么需要觸發(fā)器 理解觸發(fā)器的工作原理 掌握如何使用inserted表和deleted

3、表 掌握如何創(chuàng)建： INSERT觸發(fā)器 UPDATE觸發(fā)器 DELETE觸發(fā)器5q 為什么需要觸發(fā)器(TRIGGER)呢？典型的應(yīng)用就是銀行的取款機(jī)系統(tǒng) 為什么需要觸發(fā)器演示： 為什么需要觸發(fā)器.sql帳戶信息表bank 交易信息表transInfo 張三取錢200 問題：沒有自動(dòng)修改張三的余額最優(yōu)的解決方案就是采用觸發(fā)器：l它是一種特殊的存儲(chǔ)過程 l也具備事務(wù)的功能 l它能在多表之間執(zhí)行特殊的業(yè)務(wù)規(guī)則 張三開戶1000元,李四開戶1元 6張三李四王五趙二王三宋二劉五插入什么是觸發(fā)器 3-1刪除觸發(fā)器觸發(fā)趙二退休 趙二員工表退休員工表7q觸發(fā)器是在對(duì)表進(jìn)行插入、更新或刪除操作時(shí)自動(dòng)執(zhí)行的存儲(chǔ)過

4、程q觸發(fā)器通常用于強(qiáng)制業(yè)務(wù)規(guī)則q觸發(fā)器是一種高級(jí)約束，可以定義比用CHECK 約束更為復(fù)雜的約束 q可執(zhí)行復(fù)雜的SQL語句（if/while/case）q可引用其它表中的列 什么是觸發(fā)器 3-28q觸發(fā)器定義在特定的表上，與表相關(guān)q自動(dòng)觸發(fā)執(zhí)行q不能直接調(diào)用q是一個(gè)事務(wù)（可回滾）什么是觸發(fā)器 3-39觸發(fā)器的類型 DELETE 觸發(fā)器 INSERT 觸發(fā)器 UPDATE 觸發(fā)器10q 觸發(fā)器觸發(fā)時(shí)：q系統(tǒng)自動(dòng)在內(nèi)存中創(chuàng)建deleted表或inserted表q只讀，不允許修改；觸發(fā)器執(zhí)行完成后，自動(dòng)刪除q inserted 表 q臨時(shí)保存了插入或更新后的記錄行 q可以從inserted表中檢查插

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

6、T-SQL語句GOWITH ENCRYPTION表示加密觸發(fā)器定義的SQL文本DELETE, INSERT, UPDATE指定觸發(fā)器的類型13INSERT觸發(fā)器transInfocardIDtransType transMoney1001 00021001 0002存入 300存入 500insertedcardIDtransType transMoney1001 0001支取 200transInfocardIDtransType transMoney1001 00021001 0002存入 300存入 5001001 0001支取 200插入記錄行觸發(fā)insert觸發(fā)器。向inserted

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

8、LARE type char(4),outMoney MONEY, 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 SET currentMoney=currentMoney+outMoney WHERE cardID=myCard

9、ID .GO INSERT 觸發(fā)器示例 3-2 從inserted表中獲取交易類型、交易金額等根據(jù)交易類型，減少或增加對(duì)應(yīng)卡號(hào)的余額 16INSERT 觸發(fā)器示例 3-317transInfocardIDtransType transMoney存入 300存入 5001001 0001支取 200DELETE觸發(fā)器transInfocardIDtransType transMoney1001 00021001 0002存入 300存入 500deletedcardIDtransType transMoney1001 0001支取 200刪除記錄行觸發(fā)delete觸發(fā)器向deleted表中插入被

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

11、ackupTable) SELECT * INTO backupTable FROM deleted ELSE INSERT INTO backupTable SELECT * FROM deleted print 備份數(shù)據(jù)成功，備份表中的數(shù)據(jù)為: SELECT * FROM backupTable GO 從deleted表中獲取被刪除的交易記錄DELETE觸發(fā)器示例 3-220DELETE觸發(fā)器示例 3-321Deleted（更新前的數(shù)據(jù)）customerNamecardID currentMoney李四1000 0002 1bankcustomerName cardID currentMo

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

13、leted表中獲取l修改后的數(shù)據(jù)可以從inserted表中獲取UPDATE觸發(fā)器示例 3-123-關(guān)鍵代碼-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(afterMoney-beforeMoney)20000 BEGIN print 交易金額:+conve

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