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

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

2、 MONEY NOT NULL, -交易金額 transDate DATETIME NOT NULL, -交易日期 ),一個(gè)典型的應(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 transDate GO,/*-插入測(cè)試數(shù)據(jù)：張三開(kāi)戶，開(kāi)戶金額為；李四開(kāi)戶，開(kāi)戶金額-*/ I

3、NSERT INTO bank(customerName,cardID,currentMoney) VALUES(張三,1001 0001,1000) INSERT INTO bank(customerName,cardID,currentMoney) VALUES(李四,1001 0002,1) /*-插入測(cè)試數(shù)據(jù)：張三取錢(qián)-*/ INSERT INTO transInfo(cardID,transType,transMoney) VALUES(1001 0001,支取,200) -查看結(jié)果 SELECT * FROM bank SELECT * FROM transInfo,一個(gè)典型的應(yīng)用

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

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

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

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

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

9、BASE、LOAD LOG、RECONFIGURE、RESTORE DATABASE、RESTORE LOG,1. INSERT觸發(fā)器,插入記錄行,觸發(fā)insert觸發(fā)器。向inserted表中插入新行的副本,觸發(fā)器檢查inserted表中插入的新行數(shù)據(jù)，確定是否需要回滾或執(zhí)行其他操作,INSERT觸發(fā)器的工作原理：,INSERT 觸發(fā)器示例,問(wèn)題： 解決上述的銀行取款問(wèn)題：當(dāng)向交易信息表(transInfo)中插入一條交易信息時(shí)，我們應(yīng)自動(dòng)更新對(duì)應(yīng)帳戶的余額。,帳戶信息表bank,交易信息表transInfo,張三取錢(qián)200 問(wèn)題： 沒(méi)有自動(dòng)修改張三的余額,張三開(kāi)戶1000元,李四開(kāi)戶1元,

10、分析： 在交易信息表上創(chuàng)建INSERT觸發(fā)器 從inserted臨時(shí)表中獲取插入的數(shù)據(jù)行 根據(jù)交易類(lèi)型(transType)字段的值是存入/支取， 增加/減少對(duì)應(yīng)帳戶的余額。,INSERT 觸發(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 F

11、ROM inserted IF (type=支取) UPDATE bank SET currentMoney=currentMoney- outMoney WHERE cardID=myCardID ELSE UPDATE bank SET currentMoney = currentMoney + outMoney WHERE cardID=myCardID . GO,從inserted表中獲取交易類(lèi)型、交易金額等,根據(jù)交易類(lèi)型，減少或增加對(duì)應(yīng)卡號(hào)的余額,INSERT 觸發(fā)器示例,2. DELETE觸發(fā)器,刪除記錄行,觸發(fā)delete觸發(fā)器向deleted表中插入被刪除的副本,觸發(fā)器檢查de

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

13、ects WHERE name=backupTable) SELECT * INTO backupTable FROM deleted ELSE INSERT INTO backupTable SELECT * FROM deleted print 備份數(shù)據(jù)成功，備份表中的數(shù)據(jù)為: SELECT * FROM backupTable GO,DELETE觸發(fā)器示例,3. UPDATE觸發(fā)器,刪除記錄行,向deleted表中插入被刪除的副本,檢查deleted和inserted表中的數(shù)據(jù)，確定是否需要回滾或執(zhí)行其他操作,UPDATE觸發(fā)器的工作原理：,向inserted表中插入被添加的副本,插入記

14、錄行,問(wèn)題： 跟蹤用戶的交易，交易金額超過(guò)20000元，則取消交易，并給出錯(cuò)誤提示。,分析： 在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 i

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

16、否修改了某列的數(shù)據(jù) 使用UPDATE（列）函數(shù)檢測(cè)是否修改了某列,問(wèn)題： 交易日期一般由系統(tǒng)自動(dòng)產(chǎn)生，默認(rèn)為當(dāng)前日期。為了安全 起見(jiàn)，一般禁止修改，以防舞弊。,分析： UPDATE（列名）函數(shù)可以檢測(cè)是否修改了某列,-關(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 END GO,檢查是否修改了交易日期列tr

17、ansDate,回滾事務(wù)，撤銷(xiāo)交易,列級(jí) UPDATE 觸發(fā)器,列級(jí) UPDATE 觸發(fā)器,4. INSTEAD OF 觸發(fā)器的工作過(guò)程,INSTEAD OF 觸發(fā)器 可以在表和視圖上定義 INSTEAD OF 觸發(fā)器。INSTEAD OF 觸發(fā)器代替原觸發(fā)動(dòng)作執(zhí)行，增加了視圖上所能進(jìn)行的更新的種類(lèi) 每個(gè)表上對(duì)每個(gè)觸發(fā)動(dòng)作（INSERT、UPDATE 或 DELETE）只能定義一個(gè) INSTEAD OF 觸發(fā)器 不能在具有 WITH CHECK OPTION 選項(xiàng)的視圖上創(chuàng)建 INSTEAD OF 觸發(fā)器 INSTEAD OF 觸發(fā)器可使一般不支持更新的視圖可以被更新 截獲對(duì)視圖的操作，將其重

18、導(dǎo)向底層表 在 INSTEAD OF DELETE 觸發(fā)器中，通過(guò) deleted 表訪問(wèn)欲刪除的行；在 INSTEAD OF UPDATE 或 INSTEAD OF INSERT 觸發(fā)器中，通過(guò) inserted 表訪問(wèn)新增加的行,INSTEAD OF 觸發(fā)器的工作過(guò)程（續(xù)）,創(chuàng)建結(jié)合兩個(gè)或多個(gè)表的視圖,CREATE VIEW vw_Customers AS SELECT * FROM CustomersMexUNION SELECT * FROM CustomersGer,-創(chuàng)建觸發(fā)器，將對(duì)視圖的插入引導(dǎo)到對(duì)基本表的插入,CREATE TRIGGER tr_Customers ON vw_

19、 Customers INSTEAD OF insert AS BEGIN insert into customersger (customerid,companyname,country,phone) SELECT i.customerid,panyname,i.country,i.phone FROM INSERTED i END,INSTEAD OF 觸發(fā)器的工作過(guò)程（續(xù)）,INSTEAD OF 觸發(fā)器將插入導(dǎo)向基表,Insert into -對(duì)視圖執(zhí)行 insert 操作 vw_customers(customerid,comanyname,country,phone) Values(

20、ALFKI,Alfreds Fu,Germany,三、更改和刪除觸發(fā)器,更改觸發(fā)器 語(yǔ)法：ALTER TRIGGER 觸發(fā)器名 使用新的定義代替觸發(fā)器原有的定義，觸發(fā)動(dòng)作也可以更改 由于延遲名稱(chēng)解析，觸發(fā)器可以引用觸發(fā)器定義時(shí)不存在的表，但是創(chuàng)建觸發(fā)器時(shí)會(huì)收到一條警告信息 禁用或啟用觸發(fā)器 語(yǔ)法：ALTER TABLE 表名 ENABLE | DISABLE TRIGGER ALL | 觸發(fā)器名 , n ,更改和刪除觸發(fā)器（續(xù)）,刪除觸發(fā)器 語(yǔ)法：DROP TRIGGER 觸發(fā)器名 若關(guān)聯(lián)表被刪除，則觸發(fā)器自動(dòng)刪除,觸發(fā)器是在對(duì)表進(jìn)行插入、更新或刪除操作時(shí)自動(dòng)執(zhí)行的存

21、儲(chǔ)過(guò)程，觸發(fā)器通常用于強(qiáng)制業(yè)務(wù)規(guī)則 觸發(fā)器還是一個(gè)特殊的事務(wù)單元，當(dāng)出現(xiàn)錯(cuò)誤時(shí)，可以執(zhí)行ROLLBACK TRANSACTION回滾撤銷(xiāo)操作 觸發(fā)器一般都需要使用臨時(shí)表：deleted表和inserted表，它們存放了被刪除或插入的記錄行副本 觸發(fā)器類(lèi)型： INSERT觸發(fā)器 UPDATE觸發(fā)器 DELETE觸發(fā)器 INSTEAD OF,總結(jié),使用觸發(fā)器的考慮事項(xiàng),大部分觸發(fā)器在動(dòng)作后執(zhí)行，約束和 INSTEAD OF 觸發(fā)器是在動(dòng)作前執(zhí)行的 大部分觸發(fā)器在所定義的表上執(zhí)行 INSERT、UPDATE 或 DELETE 語(yǔ)句之后執(zhí)行，而約束是在語(yǔ)句執(zhí)行前檢查的 約束最先被檢查 如果觸發(fā)器表上存在約束，則它們?cè)谟|發(fā)器執(zhí)行之前被檢查。如果違反了約束，則觸發(fā)器不執(zhí)行 表對(duì)同一動(dòng)作可以有多個(gè)觸發(fā)器 SQL Server 2000 允許在單個(gè)表上多個(gè)觸發(fā)器的嵌套。一個(gè)表上可以有多個(gè)觸發(fā)器，每個(gè)觸發(fā)器可以定義為單個(gè)動(dòng)作或多個(gè)