SQL觸發器執行個體講解

SQL觸發器執行個體1

定義： 何為觸發器？在SQL Server裡面也就是對某一個表的一定的操作，觸發某種條件，進而執行的一段程式。觸發器是一個特殊的存儲過程。 

      常見的觸發器有三種：分别應用于Insert , Update , Delete 事件。 

      我為什麼要使用觸發器？比如，這麼兩個表： 

      Create Table Student(              --學生表 

        StudentID int primary key,       --學号 

        .... 

       ) 

      Create Table BorrowRecord(               --學生借書記錄表 

        BorrowRecord   int identity(1,1),       --流水号   

        StudentID      int ,                    --學号 

        BorrowDate     datetime,                --借出時間 

        ReturnDAte     Datetime,                --歸還時間 

        ... 

      ) 

     用到的功能有: 

        1.如果我更改了學生的學号,我希望他的借書記錄仍然與這個學生相關(也就是同時更改借書記錄表的學号); 

        2.如果該學生已經畢業，我希望删除他的學号的同時，也删除它的借書記錄。 

     等等。 

     這時候可以用到觸發器。對于1，建立一個Update觸發器： 

     Create Trigger truStudent 

       On Student                         --在Student表中建立觸發器 

       for Update                          --為什麼事件觸發 

     As                                        --事件觸發後所要做的事情 

       if Update(StudentID)            

       begin 

         Update BorrowRecord 

           Set StudentID=i.StudentID 

           From BorrowRecord br , Deleted   d ,Inserted i      --Deleted和Inserted臨時表 

           Where br.StudentID=d.StudentID 

       end        

     了解觸發器裡面的兩個臨時的表：Deleted , Inserted 。注意Deleted 與Inserted分别表示觸發事件的表“舊的一條記錄”和“新的一條記錄”。 

     一個資料庫系統中有兩個虛拟表用于存儲在表中記錄改動的資訊，分别是： 

                             虛拟表Inserted                     虛拟表Deleted 

在表記錄新增時     存放新增的記錄                         不存儲記錄 

         修改時           存放用來更新的新記錄                   存放更新前的記錄 

         删除時           不存儲記錄                             存放被删除的記錄 

     一個Update 的過程可以看作為：生成新的記錄到Inserted表，複制舊的記錄到Deleted表，然後删除Student記錄并寫入新紀錄。 

     對于2，建立一個Delete觸發器 

     Create trigger trdStudent 

       On Student 

       for Delete 

     As 

       Delete BorrowRecord 

         From BorrowRecord br , Delted d 

         Where br.StudentID=d.StudentID 

     從這兩個例子我們可以看到了觸發器的關鍵：A.2個臨時的表；B.觸發機制。 

SQL觸發器執行個體2

/* 

建立虛拟測試環境，包含：表[卷煙庫存表]，表[卷煙銷售表]。 

請大家注意跟蹤這兩個表的資料，體會觸發器到底執行了什麼業務邏輯，對資料有什麼影響。 

為了能更清晰的表述觸發器的作用，表結構存在資料備援，且不符合第三範式，這裡特此說明。 

*/ 

USE Master 

GO 

IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = ’U’ AND NAME = ’卷煙庫存表’) 

DROP TABLE 卷煙庫存表 

IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = ’U’ AND NAME = ’卷煙銷售表’) 

DROP TABLE 卷煙銷售表 

--業務規則：銷售金額 = 銷售數量 * 銷售單價 業務規則。 

CREATE TABLE 卷煙銷售表 

( 

卷煙品牌 VARCHAR(40) PRIMARY KEY NOT NULL, 

購貨商 VARCHAR(40) NULL, 

銷售數量 INT NULL, 

銷售單價 MONEY NULL, 

銷售金額 MONEY NULL 

) 

--業務規則：庫存金額 = 庫存數量 * 庫存單價 業務規則。 

CREATE TABLE 卷煙庫存表 

庫存數量 INT NULL, 

庫存單價 MONEY NULL, 

庫存金額 MONEY NULL 

--建立觸發器，示例1 

建立觸發器[T_INSERT_卷煙庫存表]，這個觸發器較簡單。 

說明： 每當[卷煙庫存表]發生 INSERT 動作，則引發該觸發器。 

觸發器功能： 強制執行業務規則，保證插入的資料中，庫存金額 = 庫存數量 * 庫存單價。 

注意： [INSERTED]、[DELETED]為系統表，不可建立、修改、删除，但可以調用。 

重要： 這兩個系統表的結構同插入資料的表的結構。 

IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = ’TR’ AND NAME = ’T_INSERT_卷煙庫存表’) 

DROP TRIGGER T_INSERT_卷煙庫存表 

CREATE TRIGGER T_INSERT_卷煙庫存表 

ON 卷煙庫存表 

FOR INSERT 

AS 

--送出事務處理 

BEGIN TRANSACTION 

--強制執行下列語句，保證業務規則 

UPDATE 卷煙庫存表 

SET 庫存金額 = 庫存數量 * 庫存單價 

WHERE 卷煙品牌 IN (SELECT 卷煙品牌 from INSERTED) 

COMMIT TRANSACTION 

針對[卷煙庫存表]，插入測試資料： 

注意，第一條資料（紅塔山新勢力）中的資料符合業務規則， 

第二條資料（紅塔山人為峰）中，[庫存金額]空，不符合業務規則， 

第三條資料（雲南映像）中，[庫存金額]不等于[庫存數量]乘以[庫存單價]，不符合業務規則。 

第四條資料庫存數量為0。 

請注意在插入資料後，檢查[卷煙庫存表]中的資料是否 庫存金額 = 庫存數量 * 庫存單價。 

INSERT INTO 卷煙庫存表(卷煙品牌,庫存數量,庫存單價,庫存金額) 

SELECT ’紅塔山新勢力’,100,12,1200 UNION ALL 

SELECT ’紅塔山人為峰’,100,22,NULL UNION ALL 

SELECT ’雲南映像’,100,60,500 UNION ALL 

SELECT ’玉溪’,0,30,0 

--查詢資料 

SELECT * FROM 卷煙庫存表 

結果集 

RecordId 卷煙品牌 庫存數量 庫存單價 庫存金額 

-------- ------------ -------- ------- --------- 

1 紅塔山新勢力 100 12.0000 1200.0000 

2 紅塔山人為峰 100 22.0000 2200.0000 

3 雲南映像 100 60.0000 6000.0000 

4 玉溪 0 30.0000 .0000 

（所影響的行數為 4 行） 

--觸發器示例2 

建立觸發器[T_INSERT_卷煙銷售表]，該觸發器較複雜。 

說明: 每當[卷煙庫存表]發生 INSERT 動作，則引發該觸發器。 

觸發器功能： 實作業務規則。 

業務規則: 如果銷售的卷煙品牌不存在庫存或者庫存為零，則傳回錯誤。 

否則則自動減少[卷煙庫存表]中對應品牌卷煙的庫存數量和庫存金額。 

IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = ’TR’ AND NAME = ’T_INSERT_卷煙銷售表’) 

DROP TRIGGER T_INSERT_卷煙銷售表 

CREATE TRIGGER T_INSERT_卷煙銷售表 

ON 卷煙銷售表 

--檢查資料的合法性：銷售的卷煙是否有庫存，或者庫存是否大于零 

IF NOT EXISTS ( 

SELECT 庫存數量 

FROM 卷煙庫存表 

WHERE 卷煙品牌 IN (SELECT 卷煙品牌 FROM INSERTED) 

BEGIN 

--傳回錯誤提示 

RAISERROR(’錯誤！該卷煙不存在庫存，不能銷售。’,16,1) 

--復原事務 

ROLLBACK 

RETURN 

END 

IF EXISTS ( 

WHERE 卷煙品牌 IN (SELECT 卷煙品牌 FROM INSERTED) AND 

庫存數量 <= 0 

RAISERROR(’錯誤！該卷煙庫存小于等于0，不能銷售。’,16,1) 

--對合法的資料進行處理 

UPDATE 卷煙銷售表 

SET 銷售金額 = 銷售數量 * 銷售單價 

DECLARE @卷煙品牌 VARCHAR(40) 

SET @卷煙品牌 = (SELECT 卷煙品牌 FROM INSERTED) 

DECLARE @銷售數量 MONEY 

SET @銷售數量 = (SELECT 銷售數量 FROM INSERTED) 

SET 庫存數量 = 庫存數量 - @銷售數量, 

庫存金額 = (庫存數量 - @銷售數量)*庫存單價 

WHERE 卷煙品牌 = @卷煙品牌 

--請大家自行跟蹤[卷煙庫存表]和[卷煙銷售表]的資料變化。 

--針對[卷煙銷售表]，插入第一條測試資料，該資料是正常的。 

INSERT INTO 卷煙銷售表(卷煙品牌,購貨商,銷售數量,銷售單價,銷售金額) 

SELECT ’紅塔山新勢力’,’某購貨商’,10,12,1200 

--針對[卷煙銷售表]，插入第二條測試資料，該資料 銷售金額 不等于 銷售單價 * 銷售數量。 

--觸發器将自動更正資料，使 銷售金額 等于 銷售單價 * 銷售數量。 

SELECT ’紅塔山人為峰’,’某購貨商’,10,22,2000 

--針對[卷煙銷售表]，插入第三條測試資料，該資料中的卷煙品牌在 卷煙庫存表中找不到對應。 

--觸發器将報錯。 

SELECT ’紅河V8’,’某購貨商’,10,60,600 

伺服器: 消息 50000，級别 16，狀态 1，過程 T_INSERT_卷煙銷售表，行 15 

錯誤！該卷煙不存在庫存，不能銷售。 

--針對[卷煙銷售表]，插入第三條測試資料，該資料中的卷煙品牌在 卷煙庫存表中庫存為0。 

SELECT ’玉溪’,’某購貨商’,10,30,300 

伺服器: 消息 50000，級别 16，狀态 1，過程 T_INSERT_卷煙銷售表，行 29 

錯誤！該卷煙庫存小于等于0，不能銷售。 

SELECT * FROM 卷煙銷售表 

補充： 

1、本示例主要通過一個簡單的業務規則實作來進行觸發器使用的說明，具體的要根據需要靈活處理； 

2、關于觸發器要了解并運用好 INSERTED ，DELETED 兩個系統表； 

3、本示例建立的觸發器都是 FOR INSERT ,具體的文法可參考： 

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

                                                      Trigger文法

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

CREATE TRIGGER trigger_name 

ON { table | view } 

[ WITH ENCRYPTION ] --用于加密觸發器 

{ 

{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] } 

[ WITH APPEND ] 

[ NOT FOR REPLICATION ] 

[ { IF UPDATE ( column ) 

[ { AND | OR } UPDATE ( column ) ] 

[ ...n ] 

| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask ) 

{ comparison_operator } column_bitmask [ ...n ] 

} ] 

sql_statement [ ...n ] 

} 

4、關于觸發器，還應該注意 

(1)、DELETE 觸發器不能捕獲 TRUNCATE TABLE 語句。 

(2)、觸發器中不允許以下 Transact-SQL 語句： 

ALTER DATABASE CREATE DATABASE DISK INIT 

DISK RESIZE DROP DATABASE LOAD DATABASE 

LOAD LOG RECONFIGURE RESTORE DATABASE 

RESTORE LOG 

(3)、觸發器最多可以嵌套 32 層。 

--修改觸發器 

--實質上，是将 CREATE TRIGGER ... 修改為 ALTER TRIGGER ...即可。 

--删除觸發器 

DROP TRIGGER xxx 

--删除測試環境 

################################################################## 

觸發器的基礎知識和例子 

：create trigger tr_name 

on table/view 

{for | after | instead of } [update][,][insert][,][delete] 

[with encryption] 

as {batch | if update (col_name) [{and|or} update (col_name)] } 

說明： 

1 tr_name ：觸發器名稱 

2 on table/view ：觸發器所作用的表。一個觸發器隻能作用于一個表 

3 for 和after ：同義 

4 after 與instead of :sql 2000新增項目afrer 與 instead of 的差別 

After 

在觸發事件發生以後才被激活,隻可以建立在表上 

Instead of 

代替了相應的觸發事件而被執行,既可以建立在表上也可以建立在視圖上 

5 insert、update、delete：激活觸發器的三種操作，可以同時執行，也可選其一 

6 if update (col_name)：表明所作的操作對指定列是否有影響，有影響，則激活觸發器。此外，因為delete 操作隻對行有影響， 

是以如果使用delete操作就不能用這條語句了(雖然使用也不出錯，但是不能激活觸發器，沒意義)。 

7 觸發器執行時用到的兩個特殊表：deleted ,inserted 

deleted 和inserted 可以說是一種特殊的臨時表，是在進行激活觸發器時由系統自動生成的，其結構與觸發器作用的表結構是一 

樣的，隻是存放 的資料有差異。 

續 

下面表格說明deleted 與inserted 資料的差異 

deleted 與inserted 資料的差異 

Inserted 

存放進行insert和update 操作後的資料 

Deleted 

存放進行delete 和update操作前的資料 

注意：update 操作相當于先進行delete 再進行insert ,是以在進行update操作時，修改前的資料拷貝一條到deleted 表中，修改後 

的資料在存到觸發器作用的表的同時，也同時生成一條拷貝到insered表中

/////////

CREATE TRIGGER [TRIGGER admixture_receive_log] ON dbo.chl_lydj 

FOR UPDATE

AS

begin

        declare @djsfxg char(10)      declare @wtbh char(20)

        select @wtbh=wtbh from inserted

end

if (select data_sfjl from t_logsetup)='是'

declare @oldcjmc char (100)          declare @oldlyrq datetime

declare @oldbzbh char (60)             declare @oldzl char (20)

declare @olddj char (10)

declare @newcjmc char (100)          declare @newlyrq datetime

declare @newbzbh char (60)             declare @newzl char (20)

declare @newdj char (10)

             declare @xgr char (20)

             select @oldcjmc=cjmc,@oldlyrq=lyrq,@oldbzbh=bzbh,@oldzl=zl,@olddj=dj from deleted

             select @newcjmc=cjmc,@newlyrq=lyrq,@newbzbh=bzbh,@newzl=zl,@newdj=dj from inserted

             select @xgr=xgr from t_modifyuser where @wtbh=wtbh

         if @oldcjmc<>@newcjmc

             begin

                      insert 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)

             end

//////////修改時，直接把‘create’改為‘alter’即可

/////////////////////////

CREATE TRIGGER [TRIGGER ly_tzk_syf] ON dbo.ly_tzk 

FOR insert 

         declare @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   declare @feiyong_sy money 

         declare @dj char(20)

        select @wtbh=wtbh , @clmc=clmc , @dwbh=dwbh ,@syf=syf from inserted

        set @dj_1=convert(money ,@dj)

       if @dj_1 <>0 

       begin

             set @feiyong_xf=@feiyong_xf+@dj_1

             set @feiyong_sy=@feiyong_sy-@dj_1

       end

//////////////////////

CREATE TRIGGER [TRIGGER ly_tzk_syf_shanchu] ON dbo.ly_tzk 

FOR delete 

         declare @clmc char(100)     declare @dwbh char(100)    declare @dwgcbh char(100) declare @wtbh char(50) 

         declare @feiyong_z money    declare @feiyong_xf money   declare @feiyong_sy money 

         declare @syf char(100)      declare @syf_1 money

         --declare @dj char(20)   declare @dj_1 money 

        set @syf_1=convert(money ,@syf)

       if @syf_1 <>0 

             set @feiyong_xf=@feiyong_xf-@syf_1

             set @feiyong_sy=@feiyong_sy+@syf_1