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

Delete BorrowRecord

From BorrowRecord br , Delted d

Where br.StudentID=d.StudentID

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

SQL觸發器執行個體2

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

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

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

USE Master

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

--送出事務處理

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

begin

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

select @wtbh=wtbh from inserted

update ly_tzk set djsfxg='已修改' where wtbh=@wtbh

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

select @dj=dj from feihao_bz where clmc=@clmc

select @feiyong_z=feiyong_z, @feiyong_xf=feiyong_xf, @feiyong_sy=feiyong_sy from gongchengxinxi where dwgcbh=@dwbh

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

update ly_tzk set syf=@dj where wtbh=@wtbh

update gongchengxinxi set feiyong_xf=@feiyong_xf, feiyong_sy=@feiyong_sy where dwgcbh=@dwbh

end

else update ly_tzk set syf=convert(char , 0.0) where wtbh=@wtbh

SQL觸發器執行個體1

繼續閱讀

阿裡雲天池龍珠計劃SQL訓練營打卡

阿裡雲天池龍珠計劃SQL訓練營day1

實驗樓sql進階之成績管理系統的資料操作(window)

自學Zabbix3.10.2-事件通知Notifications upon events-Actions報警配置點選傳回：自學zabbix集錦

HDU 5678 ztr loves trees

FZU 2086 餐廳點餐

拓端tecdat|R語言彈性網絡Elastic Net正則化懲罰回歸模型交叉驗證可視化

二叉樹及其應用--二叉樹建立

Oracle的基本操作

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

關于SQL語言

SQL語言基礎：常用的資料查詢語句

neo4j之cypher使用文檔

詳解STM32單片機的堆棧

sqlServer根據經緯查距離