SQL Server 觸發(fā)器PPT課件.ppt

1、 觸發(fā)器,1,回顧 2-1,代碼閱讀，下列系統(tǒng)存儲過程的功能是： EXEC sp_helpconstraint stuInfo EXEC sp_helpindex stuMarks EXEC sp_renamedb stuDB,studentDB EXEC xp_cmdshell mkdir d:project 存儲過程的參數分為哪兩類？ 根據您的理解，print語句和RAISERROR語句有什么區(qū)別,2,回顧 2-2,代碼閱讀:指出下列語句的錯誤 CREATE PROCEDURE proc_stuInfo -定義存儲過程 stuName VARCHAR(10), m INT OUTPUT,

2、stuAge=18 INT AS .代碼略 GO -調用存儲過程 DECLARE s INT EXEC proc_stuInfo 張三,s,30 EXEC proc_stuInfo 張三,s EXEC proc_stuInfo stuAge=22,stuName=李四,m=s OUTPUT,stuAge INT=18,EXEC proc_stuInfo 張三,s OUTPUT,30,EXEC proc_stuInfo 張三,s OUTPUT,正確,3,目標,了解為什么需要觸發(fā)器 理解觸發(fā)器的工作原理 掌握如何使用inserted表和deleted表 掌握如何創(chuàng)建： INSERT觸發(fā)器 UPDA

3、TE觸發(fā)器 DELETE觸發(fā)器,4,為什么需要觸發(fā)器(TRIGGER)呢？典型的應用就是銀行的取款機系統(tǒng),為什么需要觸發(fā)器,演示： 為什么需要觸發(fā)器.sql,帳戶信息表bank,交易信息表transInfo,張三取錢200 問題： 沒有自動修改張三的余額,最優(yōu)的解決方案就是采用觸發(fā)器： 它是一種特殊的存儲過程 也具備事務的功能 它能在多表之間執(zhí)行特殊的業(yè)務規(guī)則,張三開戶1000元,李四開戶1元,5,趙二,插入,什么是觸發(fā)器 3-1,刪除,觸發(fā)器觸發(fā),趙二退休,趙二,員工表,退休員工表,6,觸發(fā)器是在對表進行插入、更新或刪除操作時自動執(zhí)行的存儲過程 觸發(fā)器通常用于強制業(yè)務規(guī)則 觸發(fā)器是一種高級約

4、束，可以定義比用CHECK 約束更為復雜的約束 可執(zhí)行復雜的SQL語句（if/while/case） 可引用其它表中的列,什么是觸發(fā)器 3-2,7,觸發(fā)器定義在特定的表上，與表相關 自動觸發(fā)執(zhí)行 不能直接調用 是一個事務（可回滾）,什么是觸發(fā)器 3-3,8,觸發(fā)器的類型,DELETE 觸發(fā)器 INSERT 觸發(fā)器 UPDATE 觸發(fā)器,9,觸發(fā)器觸發(fā)時： 系統(tǒng)自動在內存中創(chuàng)建deleted表或inserted表 只讀，不允許修改；觸發(fā)器執(zhí)行完成后，自動刪除 inserted 表 臨時保存了插入或更新后的記錄行 可以從inserted表中檢查插入的數據是否滿足業(yè)務需求 如果不滿足，則向用戶報告錯

5、誤消息，并回滾插入操作 deleted 表 臨時保存了刪除或更新前的記錄行 可以從deleted表中檢查被刪除的數據是否滿足業(yè)務需求 如果不滿足，則向用戶報告錯誤消息，并回滾插入操作,inserted 和deleted 表2-1,10,inserted 和deleted 表2-2,inserted表和deleted表存放的信息,11,創(chuàng)建觸發(fā)器的語法：,如何創(chuàng)建觸發(fā)器,CREATE TRIGGER trigger_name ON table_name FOR DELETE, INSERT, UPDATE AS T-SQL語句 GO,DELETE, INSERT, UPDATE指定觸發(fā)器的類型,

6、12,INSERT觸發(fā)器,插入記錄行,觸發(fā)insert觸發(fā)器。向inserted表中插入新行的副本,觸發(fā)器檢查inserted表中插入的新行數據，確定是否需要回滾或執(zhí)行其他操作,INSERT觸發(fā)器的工作原理：,13,INSERT 觸發(fā)器示例 3-1,問題： 解決上述的銀行取款問題：當向交易信息表(transInfo)中插入一條交易信息時，我們應自動更新對應帳戶的余額。,分析： 在交易信息表上創(chuàng)建INSERT觸發(fā)器 從inserted臨時表中獲取插入的數據行 根據交易類型(transType)字段的值是存入/支取， 增加/減少對應帳戶的余額。,14,-關鍵代碼- CREATE TRIGGER t

7、rig_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 SET cur

8、rentMoney=currentMoney+outMoney WHERE cardID=myCardID . GO,INSERT 觸發(fā)器示例 3-2,從inserted表中獲取交易類型、教員金額等,根據交易類型，減少或增加對應卡號的余額,15,INSERT 觸發(fā)器示例 3-3,16,DELETE觸發(fā)器,刪除記錄行,觸發(fā)delete觸發(fā)器向deleted表中插入被刪除的副本,觸發(fā)器檢查deleted表中被刪除的數據，決定是否需要回滾或執(zhí)行其他操作,DELETE觸發(fā)器的工作原理：,17,問題： 當刪除交易信息表時，要求自動備份被刪除的數據到表backupTable中 。,分析： 在交易信息表上創(chuàng)

9、建DELETE觸發(fā)器 被刪除的數據可以從deleted表中獲取,DELETE觸發(fā)器示例 3-1,18,-關鍵代碼- CREATE TRIGGER trig_delete_transInfo ON transInfo FOR DELETE AS print 開始備份數據，請稍后. IF NOT EXISTS(SELECT * FROM sysobjects WHERE name=backupTable) SELECT * INTO backupTable FROM deleted ELSE INSERT INTO backupTable SELECT * FROM deleted print 備

10、份數據成功，備份表中的數據為: SELECT * FROM backupTable GO,從deleted表中獲取被刪除的交易記錄,DELETE觸發(fā)器示例 3-2,19,DELETE觸發(fā)器示例 3-3,20,UPDATE觸發(fā)器,刪除記錄行,向deleted表中插入被刪除的副本,檢查deleted和inserted表中的數據，確定是否需要回滾或執(zhí)行其他操作,UPDATE觸發(fā)器的工作原理：,向inserted表中插入被添加的副本,插入記錄行,21,問題： 跟蹤用戶的交易，交易金額超過20000元，則取消交易，并給出錯誤提示。,分析： 在bank表上創(chuàng)建UPDATE觸發(fā)器 修改前的數據可以從dele

11、ted表中獲取 修改后的數據可以從inserted表中獲取,UPDATE觸發(fā)器示例 3-1,22,-關鍵代碼- 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 交易金額:+con

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