SQL中如何使用觸發(fā)器,實(shí)現(xiàn)簡單的觸發(fā)功能?
點(diǎn)擊上方SQL數(shù)據(jù)庫開發(fā)，關(guān)注獲取SQL視頻教程
SQL專欄
最近有小伙伴向我請求幫助，要寫一個(gè)觸發(fā)器。我看了一下需求很明確，就是執(zhí)行更新，插入后觸發(fā)一些事件。覺得挺有意思的，于是幫他寫了一下，這里分享給大家。
表結(jié)構(gòu)
有如下四張表：
出勤
組
組類別
配置
問題
1.更新[出勤_上班時(shí)長] 如果:"出勤"表,[出勤_上班時(shí)間]或者[出勤_下班時(shí)間],列發(fā)生改變所觸發(fā)事件
更新上述兩列 "出勤"表,出勤_上班時(shí)長 = 出勤_下班時(shí)間 - 出勤_上班時(shí)間
插入上述兩列 "出勤"表,出勤_上班時(shí)長不插數(shù)據(jù)，插入完成后計(jì)算它。出勤_上班時(shí)長 = 出勤_下班時(shí)間 - 出勤_上班時(shí)間 ?
2.插入 如果:"出勤"表,[出勤_日期],列發(fā)生改變所觸發(fā)事件
插入 (配置_日期,組_名,組類別_名,組_號,組類別_號)
查詢[a.出勤_日期,b.組_名,c.組類別_名,a.組_號,c.組類別_號]
創(chuàng)建表結(jié)構(gòu)
根據(jù)給定的表結(jié)構(gòu)，我們創(chuàng)建到數(shù)據(jù)庫中
/*
時(shí)間：2018-12-26
作者：Lyven
需求：創(chuàng)建一個(gè)觸發(fā)器，完成相應(yīng)的更新和插入功能
*/
Use SQL_Road
CREATE TABLE 出勤
(ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
出勤_月份 INT ,
出勤_日期 INT ,
出勤_上班時(shí)間 VARCHAR(20),
出勤_下班時(shí)間 VARCHAR(20),
出勤_上班時(shí)長 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),
組類別_時(shí)薪 NUMERIC(18,2)
)
CREATE TABLE 配置
(ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
配置_日期 INT,
組_名 VARCHAR(20),
組類別_名 NVARCHAR(20),
配置_工時(shí) VARCHAR(20),
配置_工資 NUMERIC(18,2),
組_號 VARCHAR(10),
組類別_號 VARCHAR(10)
)
GO插入測試數(shù)據(jù)
INSERT INTO 出勤(出勤_月份,出勤_日期,出勤_上班時(shí)間,出勤_下班時(shí)間,組_號)
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 組類別(組類別_號,組類別_名,組類別_時(shí)薪)
VALUES
( '01', N'自動(dòng)', 100.00 ),
( '02', N'員工', 200.00 ),
( '03', N'學(xué)員', 150.00 )
INSERT INTO 配置(配置_日期 , 組_名, 組類別_名, 配置_工資 ,
組_號, 組類別_號)
VALUES
( 24, 'CAD', N'自動(dòng)', 12.50, '01', '01' ),
( 25, 'MAX', N'員工', 12.60, '02', '02' ),
( 26, 'U3D', N'學(xué)員', 12.70, '03', '03' )需求分析
第一個(gè)需求其實(shí)是只要上班時(shí)間和下班時(shí)間，我們就自動(dòng)給它算出這個(gè)時(shí)長，其實(shí)這樣的需求在插入的時(shí)候就可以解決，這里我們不討論這種優(yōu)化方案，只是根據(jù)這個(gè)需求看該如何寫出這個(gè)觸發(fā)器。
第二個(gè)需求則是在日期發(fā)生變動(dòng)的時(shí)候，需要對配置表插入一條數(shù)據(jù)
這樣我們可以把這兩個(gè)需求寫在一個(gè)觸發(fā)器當(dāng)中。
測試代碼
CREATE TRIGGER T_出勤 ?--創(chuàng)建 觸發(fā)器
ON 出勤
AFTER UPDATE,INSERT ?
--一個(gè)觸發(fā)器可以同時(shí)寫更新插入和刪除等動(dòng)作
AS
BEGIN
--定義變量
DECLARE @ID INT;
DECLARE @出勤_上班時(shí)間 VARCHAR(20);
DECLARE @出勤_下班時(shí)間 VARCHAR(20); ?
DECLARE @出勤_日期 INT;
--更新 ?出勤_上班時(shí)長
IF (UPDATE (出勤_上班時(shí)間) OR UPDATE (出勤_下班時(shí)間) )
--如果出勤_上班時(shí)間和出勤_下班時(shí)間發(fā)生了更新動(dòng)作，則執(zhí)行如下代碼
BEGIN
--先獲取更新后的值保留在變量中，其中inserted表為系統(tǒng)表，存放更新后的值
?SELECT
?@ID=ID,
?@出勤_上班時(shí)間=出勤_上班時(shí)間,
?@出勤_下班時(shí)間=出勤_下班時(shí)間
?FROM inserted;
--將變量傳入到表中，使取到的值唯一，對出勤_上班時(shí)長進(jìn)行更新
UPDATE 出勤 SET 出勤_上班時(shí)長=
CONVERT(varchar(100) , DATEADD(ss, DATEDIFF(ss, 出勤_上班時(shí)間, 出勤_下班時(shí)間), 0), 108)
WHERE ID=@ID
AND (出勤_上班時(shí)間=@出勤_上班時(shí)間
OR 出勤_下班時(shí)間=@出勤_下班時(shí)間);
END
--插入配置信息
IF UPDATE (出勤_日期)
--當(dāng)出勤_日期發(fā)生了變動(dòng)，我們執(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ā)器的語法這個(gè)必須掌握，本案例是在SQL Server下執(zhí)行的，其他關(guān)系數(shù)據(jù)庫的語法可能不同，請注意一下。
2、觸發(fā)器中可以實(shí)現(xiàn)多種不同的操作，更新，刪除，插入均可寫在一個(gè)觸發(fā)器上，當(dāng)然要視情況而定
3、觸發(fā)器在執(zhí)行時(shí)會(huì)將更新前的數(shù)據(jù)存放在臨時(shí)表deleted中，在更新后會(huì)將數(shù)據(jù)存放在臨時(shí)表inserted中，這里我們就用到了臨時(shí)表inserted
4、在更新上班時(shí)長時(shí)用到了時(shí)間處理函數(shù)DATEDIFF和DATEADD，兩個(gè)函數(shù)是比較常用的時(shí)間處理函數(shù)，必須掌握。
5、參數(shù)傳遞是代碼中比較重要一環(huán)，我們是先將臨時(shí)表中的數(shù)據(jù)存放在一個(gè)變量中保存，在我們真正進(jìn)行更新或插入操作時(shí)候再把這個(gè)變量取出來使用，就是將變量再次傳遞給條件語句。
測試功能
1、在測試數(shù)據(jù)之前，我們先看看出勤表和配置表中的數(shù)據(jù)
出勤
我們看到出勤_上班時(shí)長是沒有數(shù)據(jù)的，下面我們開始更新
UPDATE 出勤 SET 出勤_上班時(shí)間='7:00'
WHERE ID=1執(zhí)行完后我們再看出勤表中的數(shù)據(jù)是否有變化
從上圖可以看出，結(jié)果符合我們預(yù)期，同理更新下班時(shí)間也會(huì)對上班時(shí)長進(jìn)行更新操作，這里就不演示了。
2、我們插入數(shù)據(jù)是否也會(huì)更新上班時(shí)長呢？我們執(zhí)行如下語句
INSERT INTO 出勤(出勤_月份,出勤_日期,
出勤_上班時(shí)間,出勤_下班時(shí)間,組_號)
VALUES (12,11,'8:30','12:00','01')執(zhí)行完后我們查看一下結(jié)果
結(jié)果也符合我們的預(yù)期。
3、當(dāng)出勤表中的日期被更新的時(shí)候，配置表里是否會(huì)插入了一條數(shù)據(jù)？我們先看看配置表中的數(shù)據(jù)
我們對出勤表中的日期進(jìn)行更新操作，看配置表會(huì)不會(huì)多一條記錄？
UPDATE dbo.出勤 SET 出勤_日期='22'
WHERE ID=2更新后我們看看配置表中的數(shù)據(jù)
結(jié)果也符合我們的預(yù)期。
總結(jié)
整個(gè)案例其實(shí)精華部分就只是觸發(fā)器部分，但是為了讓小伙伴們能更加清晰的閱讀和思考，故將整個(gè)案例從需求到測試都給大家展現(xiàn)出來。而觸發(fā)器部分如果對其語法比較了解，使用起來并沒有想象的那么難。
當(dāng)然其中有一些小技巧還是需要大家去了解一下，就比如將更新和插入操作寫在一個(gè)觸發(fā)器是可以的。我們也可以指定只有哪幾列發(fā)生更新操作的時(shí)候才執(zhí)行相應(yīng)的語句。最后，如果你有一些比較經(jīng)典的需求，也可以發(fā)送給我，興許下次展示的就是你的案例啦！
后臺回復(fù)關(guān)鍵字：資料領(lǐng)取，獲取一份精心整理的技術(shù)干貨
后臺回復(fù)關(guān)鍵字：進(jìn)群，帶你進(jìn)入高手如云的交流群。
推薦閱讀
點(diǎn)擊「閱讀原文」了解SQL訓(xùn)練營