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

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

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

3、160;   int ,                    -學號 BorrowDate     datetime,                -借出時間 ReturnDA

4、te     Datetime,                -歸還時間 . ) 用到的功能有: 1.如果我更改了學生的學號,我希望他的借書記錄仍然與這個學生相關(也就是同時更改借書記錄表的學號); 2.如果該學生已經(jīng)畢業(yè)，我希望刪除他的學號的同時，也刪除它的借書記錄。 等等。 這時候可以用到觸發(fā)器。對于1，創(chuàng)建一個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臨時表 Where br.StudentID=d.StudentID end        理解觸發(fā)器里面的兩個臨時的表：Deleted , Inserted 。注意Deleted 與Inserted分別表示觸發(fā)事件的表“舊的一條記錄”和“新的一條記錄”。 一個數(shù)據(jù)庫系統(tǒng)中有兩個虛擬表用于存儲在表中記錄改動的信息，分別是： 虛擬表Inserted          

9、;           虛擬表Deleted 在表記錄新增時     存放新增的記錄                         不存儲記錄 修改時    

10、       存放用來更新的新記錄                   存放更新前的記錄 刪除時           不存儲記錄          &

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

12、BorrowRecord From BorrowRecord br , Delted d Where br.StudentID=d.StudentID 從這兩個例子我們可以看到了觸發(fā)器的關鍵：A.2個臨時的表；B.觸發(fā)機制。 SQL觸發(fā)器實例2/* 建立虛擬測試環(huán)境，包含：表卷煙庫存表，表卷煙銷售表。 請大家注意跟蹤這兩個表的數(shù)據(jù)，體會觸發(fā)器到底執(zhí)行了什么業(yè)務邏輯，對數(shù)據(jù)有什么影響。 為了能更清晰的表述觸發(fā)器的作用，表結(jié)構存在數(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è)務規(guī)則：銷售金額 = 銷售數(shù)量 * 銷售單價 業(yè)務規(guī)則。 CREATE TABLE 卷煙銷售表 ( 卷煙品牌 VAR

14、CHAR(40) PRIMARY KEY NOT NULL, 購貨商 VARCHAR(40) NULL, 銷售數(shù)量 INT NULL, 銷售單價 MONEY NULL, 銷售金額 MONEY NULL ) GO -業(yè)務規(guī)則：庫存金額 = 庫存數(shù)量 * 庫存單價 業(yè)務規(guī)則。 CREATE TABLE 卷煙庫存表 ( 卷煙品牌 VARCHAR(40) PRIMARY KEY NOT NULL, 庫存數(shù)量 INT NULL, 庫存單價 MONEY NULL, 庫存金額 M

15、ONEY NULL ) GO -創(chuàng)建觸發(fā)器，示例1 /* 創(chuàng)建觸發(fā)器T_INSERT_卷煙庫存表，這個觸發(fā)器較簡單。 說明： 每當卷煙庫存表發(fā)生 INSERT 動作，則引發(fā)該觸發(fā)器。 觸發(fā)器功能： 強制執(zhí)行業(yè)務規(guī)則，保證插入的數(shù)據(jù)中，庫存金額 = 庫存數(shù)量 * 庫存單價。 注意： INSERTED、DELETED為系統(tǒng)表，不可創(chuàng)建、修改、刪除，但可以調(diào)用。 重要： 這兩個系統(tǒng)表的結(jié)構同插入數(shù)據(jù)的表的結(jié)構。 */ 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 -提交事務處理 BEGIN TRANSACTION -強制執(zhí)行下列語句，保證業(yè)務規(guī)則 UPDATE 卷煙庫存表 SET 庫存金額 = 庫存數(shù)量 * 庫存單價 WHERE 卷煙品牌 IN (SELECT 卷煙品牌 from

17、 INSERTED) COMMIT TRANSACTION GO /* 針對卷煙庫存表，插入測試數(shù)據(jù)： 注意，第一條數(shù)據(jù)（紅塔山新勢力）中的數(shù)據(jù)符合業(yè)務規(guī)則， 第二條數(shù)據(jù)（紅塔山人為峰）中，庫存金額空，不符合業(yè)務規(guī)則， 第三條數(shù)據(jù)（云南映像）中，庫存金額不等于庫存數(shù)量乘以庫存單價，不符合業(yè)務規(guī)則。 第四條數(shù)據(jù)庫存數(shù)量為0。 請注意在插入數(shù)據(jù)后，檢查卷煙庫存表中的數(shù)據(jù)是否 庫存金額 = 庫存數(shù)量 * 庫存單價。 */ INSERT INTO 卷煙庫存表(卷煙品牌,庫存數(shù)量,庫存單價,庫存

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ù)量 庫存單價 庫存金額 - - - - - 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ā)生 INSERT 動作，則引發(fā)該觸發(fā)器。 觸發(fā)器功能： 實現(xiàn)業(yè)務規(guī)則。 業(yè)務規(guī)則: 如果銷售的卷煙品牌不存在庫存或者庫存為零，則返回錯誤。 否則則自動減少卷煙庫存表中對應品牌卷煙的

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 -返回錯誤提示 RAISERROR(錯誤！該卷煙不存在庫存，不能銷售。,16,1) -回滾事務 ROLLBACK RETURN END IF EXISTS ( SELECT 庫存數(shù)量 FROM 卷煙庫存表 WHERE 卷煙品牌 IN (SELECT 卷煙品牌 FROM INSERTED) AND

22、60;庫存數(shù)量 <= 0 ) BEGIN -返回錯誤提示 RAISERROR(錯誤！該卷煙庫存小于等于0，不能銷售。,16,1) -回滾事務 ROLLBACK RETURN END -對合法的數(shù)據(jù)進行處理 -強制執(zhí)行下列語句，保證業(yè)務規(guī)則 UPDATE 卷煙銷售表 SET 銷售金額 = 銷售數(shù)量 * 銷售單價 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ù)量)*庫存單價 WHERE 卷煙品牌 = 卷煙品牌 COMMIT TRANSACTION GO -請大家自行跟蹤卷煙庫存表和卷煙銷售表的數(shù)據(jù)變化。 -針對卷煙銷售表，插入第一條測試數(shù)據(jù)，該數(shù)據(jù)是

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

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

26、 INTO 卷煙銷售表(卷煙品牌,購貨商,銷售數(shù)量,銷售單價,銷售金額) SELECT 玉溪,某購貨商,10,30,300 GO /* 結(jié)果集 服務器: 消息 50000，級別 16，狀態(tài) 1，過程 T_INSERT_卷煙銷售表，行 29 錯誤！該卷煙庫存小于等于0，不能銷售。 */ -查詢數(shù)據(jù) SELECT * FROM 卷煙庫存表 SELECT * FROM 卷煙銷售表 GO /* 補充： 1、本示例主要通過一個簡單的業(yè)務規(guī)則實現(xiàn)來進行觸發(fā)器使用的說明

27、，具體的要根據(jù)需要靈活處理； 2、關于觸發(fā)器要理解并運用好 INSERTED ，DELETED 兩個系統(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、關于觸發(fā)器，還應該注意 (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ā)器 -實質(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ā)器的基礎知識和例子 ：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ā)器所作用的表。一個觸發(fā)器只能作用于一個表 3 for 和after ：同義 4 after 與instead of :sql 2000新增項目afrer 與 instead of 的區(qū)別 After 在觸發(fā)事件發(fā)生以后才被激活,只可以建立在表上&#

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

34、可以說是一種特殊的臨時表，是在進行激活觸發(fā)器時由系統(tǒng)自動生成的，其結(jié)構與觸發(fā)器作用的表結(jié)構是一 樣的，只是存放 的數(shù)據(jù)有差異。 續(xù) 下面表格說明deleted 與inserted 數(shù)據(jù)的差異 deleted 與inserted 數(shù)據(jù)的差異 Inserted 存放進行insert和update 操作后的數(shù)據(jù) Deleted 存放進行delete 和update操作前的數(shù)據(jù) 注意：update 操作相當于先進行delete 再進行insert ,所以在進行update操作時，修改前的數(shù)據(jù)拷貝一條到delete

35、d 表中，修改后 的數(shù)據(jù)在存到觸發(fā)器作用的表的同時，也同時生成一條拷貝到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/修改時，直接把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