SQL觸發(fā)器實戰(zhàn)

點擊關(guān)注上方“SQL數(shù)據(jù)庫開發(fā)”，

設(shè)為“置頂或星標(biāo)”，第一時間送達(dá)干貨

最近有小伙伴向我請求幫助，要寫一個觸發(fā)器。我看了一下需求很明確，就是執(zhí)行更新，插入后觸發(fā)一些事件。覺得挺有意思的，于是幫他寫了一下，這里分享給大家。

表結(jié)構(gòu)

有如下四張表：

出勤

組

組類別

配置

問題

1.更新[出勤_上班時長] 如果:"出勤"表,[出勤_上班時間]或者[出勤_下班時間],列發(fā)生改變所觸發(fā)事件

• 更新上述兩列 "出勤"表,出勤_上班時長 = 出勤_下班時間 - 出勤_上班時間

• 插入上述兩列 "出勤"表,出勤_上班時長不插數(shù)據(jù)，插入完成后計算它。出勤_上班時長 = 出勤_下班時間 - 出勤_上班時間  

2.插入 如果:"出勤"表,[出勤_日期],列發(fā)生改變所觸發(fā)事件

插入 (配置_日期,組_名,組類別_名,組_號,組類別_號)

查詢[a.出勤_日期,b.組_名,c.組類別_名,a.組_號,c.組類別_號]

創(chuàng)建表結(jié)構(gòu)

根據(jù)給定的表結(jié)構(gòu)，我們創(chuàng)建到數(shù)據(jù)庫中

/*
時間：2021-01-25
作者：Lyven
需求：創(chuàng)建一個觸發(fā)器，完成相應(yīng)的更新和插入功能
*/
Use SQL_Road
CREATE TABLE 出勤
(ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
出勤_月份 INT ,
出勤_日期 INT ,
出勤_上班時間 VARCHAR(20),
出勤_下班時間 VARCHAR(20),
出勤_上班時長 VARCHAR(20),
組_號 VARCHAR(10)
)

CREATE TABLE 組
(ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
組_號 VARCHAR(10),
組_名 NVARCHAR(20),
組類別_號 VARCHAR(10),
組_人數(shù) INT
)

CREATE TABLE 組類別
(ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
組類別_號 VARCHAR(10),
組類別_名 NVARCHAR(20),
組類別_時薪 NUMERIC(18,2)
)

CREATE TABLE 配置
(ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
配置_日期 INT,
組_名 VARCHAR(20),
組類別_名 NVARCHAR(20),
配置_工時 VARCHAR(20),
配置_工資 NUMERIC(18,2),
組_號 VARCHAR(10),
組類別_號 VARCHAR(10)
)
GO

插入測試數(shù)據(jù)

INSERT INTO 出勤(出勤_月份,出勤_日期,出勤_上班時間,出勤_下班時間,組_號)
VALUES
( 1, 12, 24, '7:30', '12:35', '01' ),
( 2, 12, 25, '8:00', '12:28', '01' ),
( 3, 12, 26, '8:30', '12:00', '01' )

INSERT INTO 組(組_號,組_名,組類別_號,組_人數(shù))
VALUES
( '01', 'CAD', '01', 2 ),
( '02', 'MAX', '02', 1 ),
( '03', 'U3D', '03', 3 )

INSERT INTO 組類別(組類別_號,組類別_名,組類別_時薪)
VALUES
( '01', N'自動', 100.00 ),
( '02', N'員工', 200.00 ),
( '03', N'學(xué)員', 150.00 )

INSERT INTO 配置(配置_日期 , 組_名, 組類別_名, 配置_工資 ,
組_號, 組類別_號)
VALUES
( 24, 'CAD', N'自動', 12.50, '01', '01' ),
( 25, 'MAX', N'員工', 12.60, '02', '02' ),
( 26, 'U3D', N'學(xué)員', 12.70, '03', '03' )

需求分析

1. 第一個需求其實是只要上班時間和下班時間，我們就自動給它算出這個時長，其實這樣的需求在插入的時候就可以解決，這里我們不討論這種優(yōu)化方案，只是根據(jù)這個需求看該如何寫出這個觸發(fā)器。

2. 第二個需求則是在日期發(fā)生變動的時候，需要對配置表插入一條數(shù)據(jù)

這樣我們可以把這兩個需求寫在一個觸發(fā)器當(dāng)中。

測試代碼

CREATE TRIGGER T_出勤  --創(chuàng)建 觸發(fā)器
ON 出勤
AFTER UPDATE,INSERT  
--一個觸發(fā)器可以同時寫更新插入和刪除等動作
AS
BEGIN
--定義變量
DECLARE @ID INT;
DECLARE @出勤_上班時間 VARCHAR(20);
DECLARE @出勤_下班時間 VARCHAR(20);  
DECLARE @出勤_日期 INT;

--更新  出勤_上班時長
IF (UPDATE (出勤_上班時間) OR UPDATE (出勤_下班時間) )
--如果出勤_上班時間和出勤_下班時間發(fā)生了更新動作，則執(zhí)行如下代碼
BEGIN
--先獲取更新后的值保留在變量中，其中inserted表為系統(tǒng)表，存放更新后的值
 SELECT
 @ID=ID,
 @出勤_上班時間=出勤_上班時間,
 @出勤_下班時間=出勤_下班時間
 FROM inserted;
--將變量傳入到表中，使取到的值唯一，對出勤_上班時長進(jìn)行更新
UPDATE 出勤 SET 出勤_上班時長=
CONVERT(varchar(100) , DATEADD(ss, DATEDIFF(ss, 出勤_上班時間, 出勤_下班時間), 0), 108)
WHERE ID=@ID
AND (出勤_上班時間=@出勤_上班時間
OR 出勤_下班時間=@出勤_下班時間);
END

--插入配置信息
IF UPDATE (出勤_日期)
--當(dāng)出勤_日期發(fā)生了變動，我們執(zhí)行如下更新。
BEGIN
--獲取更新后的值傳給變量
 SELECT
 @ID=ID ,
 @出勤_日期=出勤_日期
 FROM inserted;
 --執(zhí)行插入操作
INSERT INTO  配置(配置_日期,組_名,組類別_名,組_號,組類別_號)
 SELECT
 a.出勤_日期,b.組_名,c.組類別_名,a.組_號,c.組類別_號
 FROM 出勤 a
 JOIN 組 b ON a.組_號 = b.組_號
 JOIN 組類別 c ON b.組類別_號 = c.組類別_號
 WHERE a.ID=@ID
 AND  a.出勤_日期=@出勤_日期  
END  
END

代碼解讀

1、觸發(fā)器的語法這個必須掌握，本案例是在SQL Server下執(zhí)行的，其他關(guān)系數(shù)據(jù)庫的語法可能不同，請注意一下。

2、觸發(fā)器中可以實現(xiàn)多種不同的操作，更新，刪除，插入均可寫在一個觸發(fā)器上，當(dāng)然要視情況而定

3、觸發(fā)器在執(zhí)行時會將更新前的數(shù)據(jù)存放在臨時表deleted中，在更新后會將數(shù)據(jù)存放在臨時表inserted中，這里我們就用到了臨時表inserted

4、在更新上班時長時用到了時間處理函數(shù)DATEDIFF和DATEADD，兩個函數(shù)是比較常用的時間處理函數(shù)，必須掌握。

5、參數(shù)傳遞是代碼中比較重要一環(huán)，我們是先將臨時表中的數(shù)據(jù)存放在一個變量中保存，在我們真正進(jìn)行更新或插入操作時候再把這個變量取出來使用，就是將變量再次傳遞給條件語句。

測試功能

1、在測試數(shù)據(jù)之前，我們先看看出勤表和配置表中的數(shù)據(jù)

出勤

我們看到出勤_上班時長是沒有數(shù)據(jù)的，下面我們開始更新

UPDATE 出勤 SET 出勤_上班時間='7:00'
WHERE ID=1

執(zhí)行完后我們再看出勤表中的數(shù)據(jù)是否有變化

從上圖可以看出，結(jié)果符合我們預(yù)期，同理更新下班時間也會對上班時長進(jìn)行更新操作，這里就不演示了。

2、我們插入數(shù)據(jù)是否也會更新上班時長呢？我們執(zhí)行如下語句

INSERT INTO 出勤(出勤_月份,出勤_日期,
出勤_上班時間,出勤_下班時間,組_號)
VALUES (12,11,'8:30','12:00','01')

執(zhí)行完后我們查看一下結(jié)果

結(jié)果也符合我們的預(yù)期。

3、當(dāng)出勤表中的日期被更新的時候，配置表里是否會插入了一條數(shù)據(jù)？我們先看看配置表中的數(shù)據(jù)

我們對出勤表中的日期進(jìn)行更新操作，看配置表會不會多一條記錄？

UPDATE dbo.出勤 SET 出勤_日期='22'
WHERE ID=2

更新后我們看看配置表中的數(shù)據(jù)

結(jié)果也符合我們的預(yù)期。

總結(jié)

整個案例其實精華部分就只是觸發(fā)器部分，但是為了讓小伙伴們能更加清晰的閱讀和思考，故將整個案例從需求到測試都給大家展現(xiàn)出來。而觸發(fā)器部分如果對其語法比較了解，使用起來并沒有想象的那么難。

當(dāng)然其中有一些小技巧還是需要大家去了解一下，就比如將更新和插入操作寫在一個觸發(fā)器是可以的。我們也可以指定只有哪幾列發(fā)生更新操作的時候才執(zhí)行相應(yīng)的語句。最后，如果你有一些比較經(jīng)典的需求，也可以發(fā)送給我，興許下次展示的就是你的案例啦！

我是岳哥，最后給大家分享我寫的SQL兩件套：《SQL基礎(chǔ)知識第二版》和《SQL高級知識第二版》的PDF電子版。里面有各個語法的解釋、大量的實例講解和批注等等，非常通俗易懂，方便大家跟著一起來實操。

有需要的讀者可以下載學(xué)習(xí)，在下面的公眾號「數(shù)據(jù)前線」(非本號)后臺回復(fù)關(guān)鍵字：SQL，就行
數(shù)據(jù)前線
——End——
后臺回復(fù)關(guān)鍵字：1024，獲取一份精心整理的技術(shù)干貨
后臺回復(fù)關(guān)鍵字：進(jìn)群，帶你進(jìn)入高手如云的交流群。
推薦閱讀
阿里徹底拆中臺了！
我的SQL學(xué)習(xí)經(jīng)歷
SQL養(yǎng)成這些好習(xí)慣是一筆財富
MySQL基本知識點梳理和查詢優(yōu)化
SQL 查詢總是先執(zhí)行SELECT語句嗎？