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' )需求分析
第一個需求其實是只要上班時間和下班時間，我們就自動給它算出這個時長，其實這樣的需求在插入的時候就可以解決，這里我們不討論這種優(yōu)化方案，只是根據(jù)這個需求看該如何寫出這個觸發(fā)器。
第二個需求則是在日期發(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)入高手如云的交流群。
推薦閱讀
