數(shù)據(jù)庫sql觸發(fā)器

1、-=- 作者：彭建軍- 日期：2005-06-27- 頁面：Page3- 概要：觸發(fā)器的創(chuàng)建、修改、刪除以及調(diào)用- 說明：以下示例均在查詢分析器下進(jìn)行，可配合圖形界面進(jìn)行測試-=-/*建立虛擬測試環(huán)境，包含：表卷煙庫存表，表卷煙銷售表。請(qǐng)大家注意跟蹤這兩個(gè)表的數(shù)據(jù)，體會(huì)觸發(fā)器到底執(zhí)行了什么業(yè)務(wù)邏輯，對(duì)數(shù)據(jù)有什么影響。為了能更清晰的表述觸發(fā)器的作用，表結(jié)構(gòu)存在數(shù)據(jù)冗余，且不符合第三范式，這里特此說明。*/USE MasterGOIF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = U AND NAME = 卷煙庫存表) DROP TABLE 卷

2、煙庫存表GOIF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = U AND NAME = 卷煙銷售表) DROP TABLE 卷煙銷售表GO-業(yè)務(wù)規(guī)則：銷售金額 = 銷售數(shù)量 * 銷售單價(jià) 業(yè)務(wù)規(guī)則。CREATE TABLE 卷煙銷售表( 卷煙品牌 VARCHAR(40) PRIMARY KEY NOT NULL, 購貨商 VARCHAR(40) NULL, 銷售數(shù)量 INT NULL, 銷售單價(jià) MONEY NULL, 銷售金額 MONEY NULL)GO-業(yè)務(wù)規(guī)則：庫存金額 = 庫存數(shù)量 * 庫存單價(jià) 業(yè)務(wù)規(guī)則。CREATE TABLE

3、 卷煙庫存表( 卷煙品牌 VARCHAR(40) PRIMARY KEY NOT NULL, 庫存數(shù)量 INT NULL, 庫存單價(jià) MONEY NULL, 庫存金額 MONEY NULL)GO-創(chuàng)建觸發(fā)器，示例1/*創(chuàng)建觸發(fā)器T_INSERT_卷煙庫存表，這個(gè)觸發(fā)器較簡單。說明： 每當(dāng)卷煙庫存表發(fā)生 INSERT 動(dòng)作，則引發(fā)該觸發(fā)器。觸發(fā)器功能： 強(qiáng)制執(zhí)行業(yè)務(wù)規(guī)則，保證插入的數(shù)據(jù)中，庫存金額 = 庫存數(shù)量 * 庫存單價(jià)。注意： INSERTED、DELETED為系統(tǒng)表，不可創(chuàng)建、修改、刪除，但可以調(diào)用。重要： 這兩個(gè)系統(tǒng)表的結(jié)構(gòu)同插入數(shù)據(jù)的表的結(jié)構(gòu)。*/IF EXISTS (SELECT

4、NAME FROM SYSOBJECTS WHERE XTYPE = TR AND NAME = T_INSERT_卷煙庫存表) DROP TRIGGER T_INSERT_卷煙庫存表GOCREATE TRIGGER T_INSERT_卷煙庫存表ON 卷煙庫存表FOR INSERTAS-提交事務(wù)處理BEGIN TRANSACTION -強(qiáng)制執(zhí)行下列語句，保證業(yè)務(wù)規(guī)則 UPDATE 卷煙庫存表 SET 庫存金額 = 庫存數(shù)量 * 庫存單價(jià) WHERE 卷煙品牌 IN (SELECT 卷煙品牌 from INSERTED)COMMIT TRANSACTIONGO/* 針對(duì)卷煙庫存表，插入測試數(shù)據(jù)：

5、注意，第一條數(shù)據(jù)（紅塔山新勢力）中的數(shù)據(jù)符合業(yè)務(wù)規(guī)則，第二條數(shù)據(jù)（紅塔山人為峰）中，庫存金額空，不符合業(yè)務(wù)規(guī)則，第三條數(shù)據(jù)（云南映像）中，庫存金額不等于庫存數(shù)量乘以庫存單價(jià)，不符合業(yè)務(wù)規(guī)則。第四條數(shù)據(jù)庫存數(shù)量為0。請(qǐng)注意在插入數(shù)據(jù)后，檢查卷煙庫存表中的數(shù)據(jù)是否 庫存金額 = 庫存數(shù)量 * 庫存單價(jià)。*/INSERT INTO 卷煙庫存表(卷煙品牌,庫存數(shù)量,庫存單價(jià),庫存金額) SELECT 紅塔山新勢力,100,12,1200 UNION ALL SELECT 紅塔山人為峰,100,22,NULL UNION ALL SELECT 云南映像,100,60,500 UNION ALL SELE

6、CT 玉溪,0,30,0GO-查詢數(shù)據(jù)SELECT * FROM 卷煙庫存表GO/*結(jié)果集RecordId 卷煙品牌 庫存數(shù)量 庫存單價(jià) 庫存金額- - - - -1 紅塔山新勢力 100 12.0000 1200.00002 紅塔山人為峰 100 22.0000 2200.00003 云南映像 100 60.0000 6000.00004 玉溪 0 30.0000 .0000（所影響的行數(shù)為 4 行）*/-觸發(fā)器示例2/*創(chuàng)建觸發(fā)器T_INSERT_卷煙銷售表，該觸發(fā)器較復(fù)雜。說明: 每當(dāng)卷煙庫存表發(fā)生 INSERT 動(dòng)作，則引發(fā)該觸發(fā)器。觸發(fā)器功能： 實(shí)現(xiàn)業(yè)務(wù)規(guī)則。業(yè)務(wù)規(guī)則: 如果銷售的卷

7、煙品牌不存在庫存或者庫存為零，則返回錯(cuò)誤。 否則則自動(dòng)減少卷煙庫存表中對(duì)應(yīng)品牌卷煙的庫存數(shù)量和庫存金額。*/IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = TR AND NAME = T_INSERT_卷煙銷售表) DROP TRIGGER T_INSERT_卷煙銷售表GOCREATE TRIGGER T_INSERT_卷煙銷售表ON 卷煙銷售表FOR INSERTASBEGIN TRANSACTION -檢查數(shù)據(jù)的合法性：銷售的卷煙是否有庫存，或者庫存是否大于零 IF NOT EXISTS ( SELECT 庫存數(shù)量 FROM 卷煙庫

8、存表 WHERE 卷煙品牌 IN (SELECT 卷煙品牌 FROM INSERTED) ) BEGIN -返回錯(cuò)誤提示 RAISERROR(錯(cuò)誤！該卷煙不存在庫存，不能銷售。,16,1) -回滾事務(wù) ROLLBACK RETURN END IF EXISTS ( SELECT 庫存數(shù)量 FROM 卷煙庫存表 WHERE 卷煙品牌 IN (SELECT 卷煙品牌 FROM INSERTED) AND 庫存數(shù)量 = 0 ) BEGIN -返回錯(cuò)誤提示 RAISERROR(錯(cuò)誤！該卷煙庫存小于等于0，不能銷售。,16,1) -回滾事務(wù) ROLLBACK RETURN END -對(duì)合法的數(shù)據(jù)進(jìn)行處理

9、 -強(qiáng)制執(zhí)行下列語句，保證業(yè)務(wù)規(guī)則 UPDATE 卷煙銷售表 SET 銷售金額 = 銷售數(shù)量 * 銷售單價(jià) WHERE 卷煙品牌 IN (SELECT 卷煙品牌 FROM INSERTED) DECLARE 卷煙品牌 VARCHAR(40) SET 卷煙品牌 = (SELECT 卷煙品牌 FROM INSERTED) DECLARE 銷售數(shù)量 MONEY SET 銷售數(shù)量 = (SELECT 銷售數(shù)量 FROM INSERTED) UPDATE 卷煙庫存表 SET 庫存數(shù)量 = 庫存數(shù)量 - 銷售數(shù)量, 庫存金額 = (庫存數(shù)量 - 銷售數(shù)量)*庫存單價(jià) WHERE 卷煙品牌 = 卷煙品牌CO

10、MMIT TRANSACTIONGO-請(qǐng)大家自行跟蹤卷煙庫存表和卷煙銷售表的數(shù)據(jù)變化。-針對(duì)卷煙銷售表，插入第一條測試數(shù)據(jù)，該數(shù)據(jù)是正常的。INSERT INTO 卷煙銷售表(卷煙品牌,購貨商,銷售數(shù)量,銷售單價(jià),銷售金額) SELECT 紅塔山新勢力,某購貨商,10,12,1200GO-針對(duì)卷煙銷售表，插入第二條測試數(shù)據(jù)，該數(shù)據(jù) 銷售金額 不等于 銷售單價(jià) * 銷售數(shù)量。-觸發(fā)器將自動(dòng)更正數(shù)據(jù)，使 銷售金額 等于 銷售單價(jià) * 銷售數(shù)量。INSERT INTO 卷煙銷售表(卷煙品牌,購貨商,銷售數(shù)量,銷售單價(jià),銷售金額) SELECT 紅塔山人為峰,某購貨商,10,22,2000GO-針對(duì)卷

11、煙銷售表，插入第三條測試數(shù)據(jù)，該數(shù)據(jù)中的卷煙品牌在 卷煙庫存表 中找不到對(duì)應(yīng)。-觸發(fā)器將報(bào)錯(cuò)。INSERT INTO 卷煙銷售表(卷煙品牌,購貨商,銷售數(shù)量,銷售單價(jià),銷售金額) SELECT 紅河V8,某購貨商,10,60,600GO/*結(jié)果集服務(wù)器: 消息 50000，級(jí)別 16，狀態(tài) 1，過程 T_INSERT_卷煙銷售表，行 15錯(cuò)誤！該卷煙不存在庫存，不能銷售。*/-針對(duì)卷煙銷售表，插入第三條測試數(shù)據(jù)，該數(shù)據(jù)中的卷煙品牌在 卷煙庫存表 中庫存為0。-觸發(fā)器將報(bào)錯(cuò)。INSERT INTO 卷煙銷售表(卷煙品牌,購貨商,銷售數(shù)量,銷售單價(jià),銷售金額) SELECT 玉溪,某購貨商,10,

12、30,300GO/*結(jié)果集服務(wù)器: 消息 50000，級(jí)別 16，狀態(tài) 1，過程 T_INSERT_卷煙銷售表，行 29錯(cuò)誤！該卷煙庫存小于等于0，不能銷售。*/-查詢數(shù)據(jù)SELECT * FROM 卷煙庫存表SELECT * FROM 卷煙銷售表GO/*補(bǔ)充：1、本示例主要通過一個(gè)簡單的業(yè)務(wù)規(guī)則實(shí)現(xiàn)來進(jìn)行觸發(fā)器使用的說明，具體的要根據(jù)需要靈活處理；2、關(guān)于觸發(fā)器要理解并運(yùn)用好 INSERTED ，DELETED 兩個(gè)系統(tǒng)表；3、本示例創(chuàng)建的觸發(fā)器都是 FOR INSERT ,具體的語法可參考： Trigger語法 CREATE TRIGGER trigger_name ON table |

13、view WITH ENCRYPTION -用于加密觸發(fā)器 FOR | AFTER | INSTEAD OF INSERT , UPDATE WITH APPEND NOT FOR REPLICATION AS IF UPDATE ( column ) AND | OR UPDATE ( column ) .n | IF ( COLUMNS_UPDATED ( ) bitwise_operator updated_bitmask ) comparison_operator column_bitmask .n sql_statement .n 4、關(guān)于觸發(fā)器，還應(yīng)該注意(1)、DELETE 觸發(fā)器不能捕獲 TRUNCATE TABLE 語句。(2)、觸發(fā)器中不允許以下 Transact-SQL 語句： ALTER DATABASE CREATE DATABASE DISK INIT DISK RESIZE DROP DATABASE LOAD DATABASE LOAD LOG RECONFIGURE RESTORE DATABA