SQL觸發(fā)器的使用及語法

1、=以下轉(zhuǎn)定義： 何為觸發(fā)器？在SQL Server里面也就是對(duì)某一個(gè)表的一定的操作，觸發(fā)某種條件，從而執(zhí)行的一段程序。觸發(fā)器是一個(gè)特殊的存儲(chǔ)過程。 常見的觸發(fā)器有三種：分別應(yīng)用于Insert , Update , Delete 事件。 我為什么要使用觸發(fā)器？比如，這么兩個(gè)表： Create Table Student(              -學(xué)生表 StudentID int primary key, 

2、0;     -學(xué)號(hào) . ) Create Table BorrowRecord(               -學(xué)生借書記錄表 BorrowRecord   int identity(1,1),       -流水號(hào)   StudentID  &#

3、160;   int ,                    -學(xué)號(hào) BorrowDate     datetime,                -借出時(shí)間 ReturnDA

4、te     Datetime,                -歸還時(shí)間 . ) 用到的功能有: 1.如果我更改了學(xué)生的學(xué)號(hào),我希望他的借書記錄仍然與這個(gè)學(xué)生相關(guān)(也就是同時(shí)更改借書記錄表的學(xué)號(hào)); 2.如果該學(xué)生已經(jīng)畢業(yè)，我希望刪除他的學(xué)號(hào)的同時(shí)，也刪除它的借書記錄。 等等。 這時(shí)候可以用到觸發(fā)器。對(duì)于1，創(chuàng)建一個(gè)Update觸發(fā)器：

5、 Create Trigger truStudent On Student                         -在Student表中創(chuàng)建觸發(fā)器 for Update            

6、0;             -為什么事件觸發(fā) As                                   

7、;     -事件觸發(fā)后所要做的事情 if Update(StudentID)            begin Update BorrowRecord Set StudentID=i.StudentID From BorrowRecord br , Deleted   d ,Inserted i      -Deleted和I

8、nserted臨時(shí)表 Where br.StudentID=d.StudentID end        理解觸發(fā)器里面的兩個(gè)臨時(shí)的表：Deleted , Inserted 。注意Deleted 與Inserted分別表示觸發(fā)事件的表“舊的一條記錄”和“新的一條記錄”。 一個(gè)數(shù)據(jù)庫系統(tǒng)中有兩個(gè)虛擬表用于存儲(chǔ)在表中記錄改動(dòng)的信息，分別是： 虛擬表Inserted          

9、;           虛擬表Deleted 在表記錄新增時(shí)     存放新增的記錄                         不存儲(chǔ)記錄 修改時(shí)    

10、       存放用來更新的新記錄                   存放更新前的記錄 刪除時(shí)           不存儲(chǔ)記錄          &

11、#160;                  存放被刪除的記錄 一個(gè)Update 的過程可以看作為：生成新的記錄到Inserted表，復(fù)制舊的記錄到Deleted表，然后刪除Student記錄并寫入新紀(jì)錄。 對(duì)于2，創(chuàng)建一個(gè)Delete觸發(fā)器 Create trigger trdStudent On Student for Delete As Delete

12、BorrowRecord From 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)境，包含：表卷煙庫存表，表卷煙銷售表。 請(qǐng)大家注意跟蹤這兩個(gè)表的數(shù)據(jù)，體會(huì)觸發(fā)器到底執(zhí)行了什么業(yè)務(wù)邏輯，對(duì)數(shù)據(jù)有什么影響。 為了能更清晰的表述觸發(fā)器的作用，表結(jié)構(gòu)存在數(shù)據(jù)冗余，且不符合第三范式，這里特此說明。 */ USE Master 

13、;GO IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = U AND NAME = 卷煙庫存表) DROP TABLE 卷煙庫存表 GO IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = U AND NAME = 卷煙銷售表) DROP TABLE 卷煙銷售表 GO -業(yè)務(wù)規(guī)則：銷售金額 = 銷售數(shù)量 * 銷售單價(jià) 業(yè)務(wù)規(guī)則。 CREATE TABLE 卷煙銷售表 ( 卷煙品牌 VAR

14、CHAR(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, 庫存金額 M

15、ONEY 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 NAME FROM SYSOBJECT

16、S WHERE XTYPE = TR AND NAME = T_INSERT_卷煙庫存表) DROP TRIGGER T_INSERT_卷煙庫存表 GO CREATE TRIGGER T_INSERT_卷煙庫存表 ON 卷煙庫存表 FOR INSERT AS -提交事務(wù)處理 BEGIN TRANSACTION -強(qiáng)制執(zhí)行下列語句，保證業(yè)務(wù)規(guī)則 UPDATE 卷煙庫存表 SET 庫存金額 = 庫存數(shù)量 * 庫存單價(jià) WHERE 卷煙品牌 IN (SELECT 卷煙品牌 from

17、 INSERTED) COMMIT TRANSACTION GO /* 針對(duì)卷煙庫存表，插入測試數(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。 請(qǐng)注意在插入數(shù)據(jù)后，檢查卷煙庫存表中的數(shù)據(jù)是否 庫存金額 = 庫存數(shù)量 * 庫存單價(jià)。 */ INSERT INTO 卷煙庫存表(卷煙品牌,庫存數(shù)量,庫存單價(jià),庫存

18、金額) SELECT 紅塔山新勢力,100,12,1200 UNION ALL SELECT 紅塔山人為峰,100,22,NULL UNION ALL SELECT 云南映像,100,60,500 UNION ALL SELECT 玉溪,0,30,0 GO -查詢數(shù)據(jù) SELECT * FROM 卷煙庫存表 GO /* 結(jié)果集 RecordId 卷煙品牌 庫存數(shù)量 庫存單價(jià) 庫存金額 - - - - - 1 紅塔山新勢力 100 12.0000 1200.0000&

19、#160;2 紅塔山人為峰 100 22.0000 2200.0000 3 云南映像 100 60.0000 6000.0000 4 玉溪 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)減少卷煙庫存表中對(duì)應(yīng)品牌卷煙的

20、庫存數(shù)量和庫存金額。 */ IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = TR AND NAME = T_INSERT_卷煙銷售表) DROP TRIGGER T_INSERT_卷煙銷售表 GO CREATE TRIGGER T_INSERT_卷煙銷售表 ON 卷煙銷售表 FOR INSERT AS BEGIN TRANSACTION -檢查數(shù)據(jù)的合法性：銷售的卷煙是否有庫存，或者庫存是否大于零 IF NOT EXISTS (

21、 SELECT 庫存數(shù)量 FROM 卷煙庫存表 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

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

23、;SET 卷煙品牌 = (SELECT 卷煙品牌 FROM INSERTED) DECLARE 銷售數(shù)量 MONEY SET 銷售數(shù)量 = (SELECT 銷售數(shù)量 FROM INSERTED) UPDATE 卷煙庫存表 SET 庫存數(shù)量 = 庫存數(shù)量 - 銷售數(shù)量, 庫存金額 = (庫存數(shù)量 - 銷售數(shù)量)*庫存單價(jià) WHERE 卷煙品牌 = 卷煙品牌 COMMIT TRANSACTION GO -請(qǐng)大家自行跟蹤卷煙庫存表和卷煙銷售表的數(shù)據(jù)變化。 -針對(duì)卷煙銷售表，插入第一條測試數(shù)據(jù)，該數(shù)據(jù)是

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

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

26、 INTO 卷煙銷售表(卷煙品牌,購貨商,銷售數(shù)量,銷售單價(jià),銷售金額) SELECT 玉溪,某購貨商,10,30,300 GO /* 結(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ā)器使用的說明

27、，具體的要根據(jù)需要靈活處理； 2、關(guān)于觸發(fā)器要理解并運(yùn)用好 INSERTED ，DELETED 兩個(gè)系統(tǒng)表； 3、本示例創(chuàng)建的觸發(fā)器都是 FOR INSERT ,具體的語法可參考： /                                &

28、#160;                     Trigger語法/CREATE TRIGGER trigger_name ON table | view   WITH ENCRYPTION -用于加密觸發(fā)器   FOR | AFTER | INSTEAD OF INSERT , UPDATE   WITH APPEND   NOT

29、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ā)器不能捕獲 TRUNCAT

30、E TABLE 語句。 (2)、觸發(fā)器中不允許以下 Transact-SQL 語句： ALTER DATABASE CREATE DATABASE DISK INIT DISK RESIZE DROP DATABASE LOAD DATABASE LOAD LOG RECONFIGURE RESTORE DATABASE RESTORE LOG (3)、觸發(fā)器最多可以嵌套 32 層。 */ -修改觸發(fā)器 -實(shí)質(zhì)上，是將 CREATE TRIGGER . 修改為 ALTER TRIGGER .即可。

31、0;-刪除觸發(fā)器 DROP TRIGGER xxx GO -刪除測試環(huán)境 DROP TABLE 卷煙庫存表 GO DROP TABLE 卷煙銷售表 GO DROP TRIGGER T_INSERT_卷煙庫存表 GO DROP TRIGGER T_INSERT_卷煙銷售表 GO # 觸發(fā)器的基礎(chǔ)知識(shí)和例子 ：create trigger tr_name on table/view for | after | instead of upda

32、te,insert,delete with encryption as batch | if update (col_name) and|or update (col_name)  說明： 1 tr_name ：觸發(fā)器名稱 2 on table/view ：觸發(fā)器所作用的表。一個(gè)觸發(fā)器只能作用于一個(gè)表 3 for 和after ：同義 4 after 與instead of :sql 2000新增項(xiàng)目afrer 與 instead of 的區(qū)別 After 在觸發(fā)事件發(fā)生以后才被激活,只可以建立在表上&#

33、160;Instead of 代替了相應(yīng)的觸發(fā)事件而被執(zhí)行,既可以建立在表上也可以建立在視圖上 5 insert、update、delete：激活觸發(fā)器的三種操作，可以同時(shí)執(zhí)行，也可選其一 6 if update (col_name)：表明所作的操作對(duì)指定列是否有影響，有影響，則激活觸發(fā)器。此外，因?yàn)閐elete 操作只對(duì)行有影響， 所以如果使用delete操作就不能用這條語句了(雖然使用也不出錯(cuò)，但是不能激活觸發(fā)器，沒意義)。 7 觸發(fā)器執(zhí)行時(shí)用到的兩個(gè)特殊表：deleted ,inserted deleted 和inserted

34、可以說是一種特殊的臨時(shí)表，是在進(jìn)行激活觸發(fā)器時(shí)由系統(tǒng)自動(dòng)生成的，其結(jié)構(gòu)與觸發(fā)器作用的表結(jié)構(gòu)是一 樣的，只是存放 的數(shù)據(jù)有差異。 續(xù) 下面表格說明deleted 與inserted 數(shù)據(jù)的差異 deleted 與inserted 數(shù)據(jù)的差異 Inserted 存放進(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ù)拷貝一條到delete

35、d 表中，修改后 的數(shù)據(jù)在存到觸發(fā)器作用的表的同時(shí)，也同時(shí)生成一條拷貝到insered表中/CREATE TRIGGER TRIGGER admixture_receive_log ON dbo.chl_lydj FOR UPDATEASbegindeclare djsfxg char(10)      declare wtbh char(20)select wtbh=wtbh from insertedupdate ly_tzk set djsfxg='已修改' where wtbh=wtbhen

36、dif (select data_sfjl from t_logsetup)='是'begindeclare oldcjmc char (100)          declare oldlyrq datetimedeclare oldbzbh char (60)             declare oldzl char (20)declare olddj char

37、(10)declare newcjmc char (100)          declare newlyrq datetimedeclare newbzbh char (60)             declare newzl char (20)declare newdj char (10)        

38、;     declare xgr char (20)             select oldcjmc=cjmc,oldlyrq=lyrq,oldbzbh=bzbh,oldzl=zl,olddj=dj from deletedselect newcjmc=cjmc,newlyrq=lyrq,newbzbh=bzbh,newzl=zl,newdj=dj from insertedselect xgr=xgr from t_modif

39、yuser where wtbh=wtbhif oldcjmc<>newcjmcbegininsert into t_modifylog (wtbh, mod_time, mod_table, mod_field, ori_value, now_value, mod_people) values(wtbh,getdate(), 'chl_lydj','cjmc', oldcjmc, newcjmc, xgr)endend/修改時(shí)，直接把create改為alter即可/CREATE TRIGGER TRIGGER ly_tzk_syf ON dbo.l

40、y_tzk FOR insert ASbegindeclare clmc char(100)     declare dwbh char(100) declare syf char(100)   declare dwgcbh char(100) declare wtbh char(50) declare dj_1 money     declare feiyong_z money   declare feiyong_xf money &

41、#160; declare feiyong_sy money declare dj char(20)select wtbh=wtbh , clmc=clmc , dwbh=dwbh ,syf=syf from insertedselect   dj=dj from feihao_bz where clmc=clmcselect feiyong_z=feiyong_z, feiyong_xf=feiyong_xf, feiyong_sy=feiyong_sy from gongchengxinxi where dwgcbh=dwbh        set dj_1=convert(money ,dj)if