SQL觸發(fā)器實(shí)例講解

1、SQL觸發(fā)器實(shí)例講解SQL資料2009-07-23 14:44:07 閱讀6072 評論9 字號：大中小 訂閱一個(gè)特殊的定義：何為觸發(fā)器？在SQL Serv er里面也就是對某一個(gè)表的一定的操作，觸發(fā)某種條件，從而執(zhí)行的一段程序。觸發(fā)器是 存儲過程。常見的觸發(fā)器有三種：分別應(yīng)用于Insert , Update , Delete 事件。我為什么要使用觸發(fā)器？比如，這么兩個(gè)表:Create Table Stude nt(Stude ntID in t primary key,-學(xué)號-學(xué)生借書記錄表Create Table BorrowRecord(BorrowRecord int identity

2、 (1,1),-流水號Stude ntID int ,-學(xué)號BorrowDatedatetime,-借出時(shí)間ReturnDAteDatetime,-歸還時(shí)間）用到的功能有：1. 如果我更改了學(xué)生的學(xué)號，我希望他的借書記錄仍然與這個(gè)學(xué)生相關(guān)（也就是同時(shí)更改借書記錄表的學(xué)號）;2. 如果該學(xué)生已經(jīng)畢業(yè)，我希望刪除他的學(xué)號的同時(shí)，也刪除它的借書記錄。這時(shí)候可以用到觸發(fā)器。對于1，創(chuàng)建一個(gè)Update觸發(fā)器:Create Trigger truStude ntOn Stude ntfor UpdateAsif Update(Stude ntID)begin-在Student表中創(chuàng)建觸發(fā)器-為什么事件觸

3、發(fā)-事件觸發(fā)后所要做的事情Update BorrowRecordSet Stude ntID二i.Stude ntIDFrom BorrowRecord br , DeletedWhere br.Stude ntID二d.Stude ntIDd ,ln serted i-Deleted 和 Inserted 臨時(shí)表end記錄”。一個(gè)數(shù)據(jù)庫系統(tǒng)中有兩個(gè)虛擬表用于存儲在表中記錄改動(dòng)的信息，分別是：虛擬表 Inserted虛擬表 Deleted在表記錄新增時(shí)存放新增的記錄不存儲記錄修改時(shí)存放用來更新的新記錄存放更新前的記錄刪除時(shí)不存儲記錄存放被刪除的記錄一個(gè) Update 的過程可以看作為：生成新的

4、記錄到 Inserted 表，復(fù)制舊的記錄到 Deleted 表，然后刪除 Student 記錄并寫入新紀(jì)錄。對于 2，創(chuàng)建一個(gè) Delet e 觸發(fā)器Create trigger trdStudentOn Studentfor DeleteAsDelete BorrowRecordFrom BorrowRecord br , Delted d Where br.StudentID=d.StudentID從這兩個(gè)例子我們可以看到了觸發(fā)器的關(guān)鍵：A.2個(gè)臨時(shí)的表；B.觸發(fā)機(jī)制。SQL 觸發(fā)器實(shí)例 2/*建立虛擬測試環(huán)境，包含：表 卷煙庫存表 ，表卷煙銷售表 。 請大家注意跟蹤這兩個(gè)表的數(shù)據(jù)，體會

5、觸發(fā)器到底執(zhí)行了什么業(yè)務(wù)邏輯，對數(shù)據(jù)有什么影響。 為了能更清晰的表述觸發(fā)器的作用，表結(jié)構(gòu)存在數(shù)據(jù)冗余，且不符合第三范式，這里特此說明 */USE MasterGOIF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = ?U? AND NAME = ?卷煙庫存表 ?)DROP TABLE 卷煙庫存表GOIF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = ?U? AND NAME = ?卷煙銷售表 ?)DROP TABLE 卷煙銷售表GO-業(yè)務(wù)規(guī)則：銷售金額 = 銷售數(shù)量 * 銷售單價(jià) 業(yè)務(wù)規(guī)則

6、。CREATE TA BLE 卷煙銷售表卷煙品牌 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 卷煙庫存表(卷煙品牌 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è)

7、觸發(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)。T_ 卷煙庫存表 ?)*/IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = ?TR? AND NAME = ?T_INSER DROP TRIGGER T_INSERT_ 卷煙庫存表GOCREATE TRIGGER T_INSERT_

8、 卷煙庫存表ON 卷煙庫存表FOR INSERTAS-提交事務(wù)處理BEGIN TRANSACTION-強(qiáng)制執(zhí)行下列語句，保證業(yè)務(wù)規(guī)則UPDATE 卷煙庫存表SET 庫存金額 = 庫存數(shù)量 * 庫存單價(jià)WHERE 卷煙品牌 IN (SELECT 卷煙品牌 from INSERTED)COMMIT TRANSAC TIONGO/*針對 卷煙庫存表 ，插入測試數(shù)據(jù)： 注意，第一條數(shù)據(jù)（紅塔山新勢力）中的數(shù)據(jù)符合業(yè)務(wù)規(guī)則， 第二條數(shù)據(jù)（紅塔山人為峰）中， 庫存金額 空，不符合業(yè)務(wù)規(guī)則， 第三條數(shù)據(jù)（云南映像）中， 庫存金額 不等于 庫存數(shù)量 乘以 庫存單價(jià) ，不符合業(yè)務(wù)規(guī)則。 第四條數(shù)據(jù)庫存數(shù)量為 0

9、。請注意在插入數(shù)據(jù)后，檢查 卷煙庫存表 中的數(shù)據(jù)是否 庫存金額 = 庫存數(shù)量 * 庫存單價(jià)。 */INSERT INTO 卷煙庫存表 （卷煙品牌 ,庫存數(shù)量 ,庫存單價(jià) ,庫存金額 ）SELECT ?紅塔山新勢力 ?,100,12,1200 UNION ALLSELECT ?紅塔山人為峰 ?,100,22,NULL UNION ALLSELECT ?云南映像 ?,100,60,500 UNION ALLSELECT ?玉溪 ?,0,30,0GO-查詢數(shù)據(jù)SELECT * FROM 卷煙庫存表GO/*結(jié)果集RecordId 卷煙品牌 庫存數(shù)量 庫存單價(jià) 庫存金額1 紅塔山新勢力 100 12.0

10、000 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ī)則 : 如果銷售的卷煙品牌不存在庫存或者庫存為零，則返回錯(cuò)誤。 否則則自動(dòng)減少 卷煙庫存表 中對應(yīng)品牌卷煙的庫存數(shù)量和庫存金額。*/T_ 卷煙銷售表 ?）IF EXISTS （SELECT NAME F

11、ROM SYSOBJECTS WHERE XTYPE = ?TR? AND NAME = ?T_INSERDROP TRIGGER T_INSERT_ 卷煙銷售表GOCREATE TRIGGER T_INSERT_ 卷煙銷售表ON 卷煙銷售表FOR INSERTASBEGIN TRANSACTION-檢查數(shù)據(jù)的合法性：銷售的卷煙是否有庫存，或者庫存是否大于零IF NOT EXISTS （SELECT 庫存數(shù)量FROM 卷煙庫存表WHERE 卷煙品牌 IN （SELECT 卷煙品牌 FROM INSERTED）BEGIN-返回錯(cuò)誤提示RAISERROR（? 錯(cuò)誤！該卷煙不存在庫存，不能銷售。 ?

12、,16,1）-回滾事務(wù)ROLLBACKRETURNENDIF EXISTS （SELECT 庫存數(shù)量FROM 卷煙庫存表WHERE 卷煙品牌 IN （SELECT 卷煙品牌 FROM INSERTED） AND庫存數(shù)量 <= 0）BEGIN-返回錯(cuò)誤提示RAISERROR（? 錯(cuò)誤！該卷煙庫存小于等于 0，不能銷售。 ?,16,1）-回滾事務(wù)ROLLBACKRETURNEND-對合法的數(shù)據(jù)進(jìn)行處理-強(qiáng)制執(zhí)行下列語句，保證業(yè)務(wù)規(guī)則UPDATE 卷煙銷售表SET 銷售金額 = 銷售數(shù)量 * 銷售單價(jià)WHERE 卷煙品牌 IN （SELECT 卷煙品牌 FROM INSERTED）DECLAR

13、E 卷煙品牌 VARCHAR（40）SET 卷煙品牌 = （SELEC T 卷煙品牌 FROM INSERTED）DECLARE 銷售數(shù)量 MONEYSET 銷售數(shù)量 = （SELEC T 銷售數(shù)量 FROM INSERTED）UPDATE 卷煙庫存表SET 庫存數(shù)量 = 庫存數(shù)量 - 銷售數(shù)量 , 庫存金額 = （庫存數(shù)量 - 銷售數(shù)量 ）*庫存單價(jià)WHERE 卷煙品牌 = 卷煙品牌COMMIT TRANSAC TIONGO-請大家自行跟蹤 卷煙庫存表 和 卷煙銷售表 的數(shù)據(jù)變化。-針對 卷煙銷售表 ，插入第一條測試數(shù)據(jù)，該數(shù)據(jù)是正常的。INSERT INTO 卷煙銷售表（卷煙品牌,購貨商,

14、銷售數(shù)量,銷售單價(jià),銷售金額 ）SELECT ? 紅塔山新勢力 ?,?某購貨商 ?,10,12,1200GO-針對 卷煙銷售表 ，插入第二條測試數(shù)據(jù)，該數(shù)據(jù)銷售金額 不等于 銷售單價(jià) * 銷售數(shù)量。-觸發(fā)器將自動(dòng)更正數(shù)據(jù)，使銷售金額 等于 銷售單價(jià) * 銷售數(shù)量。INSERT INTO 卷煙銷售表（卷煙品牌,購貨商,銷售數(shù)量,銷售單價(jià),銷售金額 ）SELECT ? 紅塔山人為峰 ?,?某購貨商 ?,10,22,2000GO-針對 卷煙銷售表 ，插入第三條測試數(shù)據(jù)，該數(shù)據(jù)中的卷煙品牌在卷煙庫存表中找不到對應(yīng)。-觸發(fā)器將報(bào)錯(cuò)。INSERT INTO 卷煙銷售表（卷煙品牌,購貨商,銷售數(shù)量,銷售單價(jià)

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

16、卷煙庫存小于等于 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_nameON table | v iew WITH ENCRYPTION - 用于加密觸發(fā)器 FOR | AFTER | INSTEAD OF IN

17、SERT , 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 語句：

18、ALTER DATABASE CREATE DATABASE DISK INITDISK RESIZE DROP DATABASE LOAD DATABASELOAD LOG RECONFIGURE RESTORE DATABASERESTORE LOG(3) 、觸發(fā)器最多可以嵌套 32 層。*/-修改觸發(fā)器-實(shí)質(zhì)上，是將CREATE TRIGGER . 修改為 ALTER TRIGGER . 即可。-刪除觸發(fā)器DROP TRIGGER xxxGO-刪除測試環(huán)境DROP TABLE卷煙庫存表GODROP TABLE卷煙銷售表GODROP TRIGGER T_INSERT_卷煙庫存表GODROP

19、 TRIGGER T_INSERT_卷煙銷售表GOit it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it it觸發(fā)器的基礎(chǔ)知識和例子： create trigger tr_nameon table/v iewf or | after | instead of upd

20、ate,insert,deletewith encry ptionas batch | if update (col_name) and|or update (col_name) 說明：1 tr_name ：觸發(fā)器名稱2 on table/v iew ：觸發(fā)器所作用的表。一個(gè)觸發(fā)器只能作用于一個(gè)表3 for 和 after ：同義4 after 與 instead of :sql 2000 新增項(xiàng)目 afrer 與 instead of 的區(qū)別After在觸發(fā)事件發(fā)生以后才被激活,只可以建立在表上Instead of代替了相應(yīng)的觸發(fā)事件而被執(zhí)行 , 既可以建立在表上也可以建立在視圖上5 ins

21、ert、update、delete :激活觸發(fā)器的三種操作，可以同時(shí)執(zhí)行，也可選其一6 if update (col_name):表明所作的操作對指定列是否有影響，有影響，則激活觸發(fā)器。此外，因?yàn)閐elete操作只對行有影響，所以如果使用delete操作就不能用這條語句了(雖然使用也不出錯(cuò)，但是不能激活觸發(fā)器，沒意義 )。7觸發(fā)器執(zhí)行時(shí)用到的兩個(gè)特殊表：deleted ,inserteddeleted和inserted可以說是一種特殊的臨時(shí)表，是在進(jìn)行激活觸發(fā)器時(shí)由系統(tǒng)自動(dòng)生成的，其結(jié)構(gòu)與觸發(fā)器作用的表結(jié)構(gòu)是一樣的，只是存放 的數(shù)據(jù)有差異。續(xù)下面表格說明deleted與inserted數(shù)據(jù)的差

22、異deleted與inserted 數(shù)據(jù)的差異In serted存放進(jìn)行insert和update操作后的數(shù)據(jù)Deleted存放進(jìn)行delete和update操作前的數(shù)據(jù)注意：update操作相當(dāng)于先進(jìn)行 delete再進(jìn)行insert ,所以在進(jìn)行update操作時(shí)，修改前的數(shù)據(jù)拷貝一條到deleted表中，修改后的數(shù)據(jù)在存到觸發(fā)器作用的表的同時(shí)，也同時(shí)生成一條拷貝到insered表中/CREATE TRIGGER TRIGGER admixture_receiv e_log ON dbo.chl_ly djFOR UPDATEASbegi ndeclare djsf xg char(10)

23、declare wtbh char(20)select wtbh=wtbh f rom in sertedupdate ly _tzk set djsfxg二'已修改'where wtbh=wtbh endif (select data_sf jl from t_logsetup)=' 是'begi ndeclare oldcjmc char (100)declare oldly rq datetimedeclare oldbzbh char (60)declare oldzl char (20)declare olddj char (10)declare n

24、ewcjmc char (100)declare newly rq datetimedeclare n ewbzbh char (60)declare n ewzl char (20)declare n ewdj char (10)declare xgr char (20)select oldcjmc=cjmc,oldlyrq=lyrq,oldbzbh=bzbh,oldzl=zl,olddj=dj from deletedselect n ewcjmc=cjmc, n ewlyrq=ly rq,newbzbh=bzbh,newzl=zl,newdj=dj from in sertedselec

25、t xgr=xgr from t_modifyuser where wtbh=wtbhif oldcjmc< >n ewcjmcbegi ninsert into t_modify log (wtbh, mod_time, mod_table, mod_f ield, ori_v alue, now_v alue, mod_people) v alues (wtbh,getdate(), 'chl_ly dj','cjmc', oldcjmc, n ewcjmc, xgr)end end/修改時(shí)，直接把，create?改為,alter?即可/CREA

26、TE TRIGGER TRIGGER ly _tzk_syf ON dbo.ly _tzkFOR in sertASbegi ndeclare clmc char(100) declare dwbh char(100) declare syf char(100)declare dwgcbh char(100) declare wtbhchar(50)declare dj_1 money declare f eiy ong_z money declare feiyong_xf money declare f eiyong_sy moneydeclare dj char(20)select wtb

27、h=wtbh , clmc=clmc , dwbh=dwbh ,syf=syf from in sertedselect dj=dj f rom f eihao_bz where clmc=clmcselect feiy ong_z=f eiy ong_z, f eiyong_xf =f eiyong_xf, feiyong_sy =feiy ong_sy from gongchengxinxi where dwgcbh=dwbhset dj_1=conv ert(money ,dj)if dj_1 <>0beginset f eiyong_xf =feiyong_xf +dj_1set f eiy ong_sy =feiy ong_sy -dj_1update ly _tzk set syf=dj where wtbh=wtbhupdate gongchengxinxi set fei