[[415489]]

本文轉(zhuǎn)載自微信公眾號(hào)「SQL數(shù)據(jù)庫(kù)開(kāi)發(fā)」，作者丶平凡世界。轉(zhuǎn)載本文請(qǐng)聯(lián)系SQL數(shù)據(jù)庫(kù)開(kāi)發(fā)公眾號(hào)。

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

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

有如下四張表：

出勤

組

組類(lèi)別

配置

問(wèn)題

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

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

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

插入 (配置_日期,組_名,組類(lèi)別_名,組_號(hào),組類(lèi)別_號(hào))

查詢(xún)[a.出勤_日期,b.組_名,c.組類(lèi)別_名,a.組_號(hào),c.組類(lèi)別_號(hào)]

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

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

/* 
時(shí)間：2021-01-25 
作者：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í)長(zhǎng) VARCHAR(20), 
組_號(hào) VARCHAR(10) 
) 
 
CREATE TABLE 組 
(ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, 
組_號(hào) VARCHAR(10), 
組_名 NVARCHAR(20), 
組類(lèi)別_號(hào) VARCHAR(10), 
組_人數(shù) INT 
) 
 
CREATE TABLE 組類(lèi)別 
(ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, 
組類(lèi)別_號(hào) VARCHAR(10), 
組類(lèi)別_名 NVARCHAR(20), 
組類(lèi)別_時(shí)薪 NUMERIC(18,2) 
) 
 
CREATE TABLE 配置 
(ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, 
配置_日期 INT, 
組_名 VARCHAR(20), 
組類(lèi)別_名 NVARCHAR(20), 
配置_工時(shí) VARCHAR(20), 
配置_工資 NUMERIC(18,2), 
組_號(hào) VARCHAR(10), 
組類(lèi)別_號(hào) VARCHAR(10) 
) 
GO 

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

INSERT INTO 出勤(出勤_月份,出勤_日期,出勤_上班時(shí)間,出勤_下班時(shí)間,組_號(hào)) 
VALUES 
( 12, 24, '7:30', '12:35', '01' ), 
( 12, 25, '8:00', '12:28', '01' ), 
( 12, 26, '8:30', '12:00', '01' ) 
 
INSERT INTO 組(組_號(hào),組_名,組類(lèi)別_號(hào),組_人數(shù)) 
VALUES 
( '01', 'CAD', '01', 2 ), 
( '02', 'MAX', '02', 1 ), 
( '03', 'U3D', '03', 3 ) 
 
INSERT INTO 組類(lèi)別(組類(lèi)別_號(hào),組類(lèi)別_名,組類(lèi)別_時(shí)薪) 
VALUES 
( '01', N'自動(dòng)', 100.00 ), 
( '02', N'員工', 200.00 ), 
( '03', N'學(xué)員', 150.00 ) 
 
INSERT INTO 配置(配置_日期 , 組_名, 組類(lèi)別_名, 配置_工資 , 
組_號(hào), 組類(lèi)別_號(hào)) 
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' ) 

需求分析

1. 第一個(gè)需求其實(shí)是只要上班時(shí)間和下班時(shí)間，我們就自動(dòng)給它算出這個(gè)時(shí)長(zhǎng)，其實(shí)這樣的需求在插入的時(shí)候就可以解決，這里我們不討論這種優(yōu)化方案，只是根據(jù)這個(gè)需求看該如何寫(xiě)出這個(gè)觸發(fā)器。
2. 第二個(gè)需求則是在日期發(fā)生變動(dòng)的時(shí)候，需要對(duì)配置表插入一條數(shù)據(jù)

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

測(cè)試代碼

CREATE TRIGGER T_出勤  --創(chuàng)建 觸發(fā)器 
ON 出勤 
AFTER UPDATE,INSERT   
--一個(gè)觸發(fā)器可以同時(shí)寫(xiě)更新插入和刪除等動(dòng)作 
AS 
BEGIN 
--定義變量 
DECLARE @ID INT; 
DECLARE @出勤_上班時(shí)間 VARCHAR(20); 
DECLARE @出勤_下班時(shí)間 VARCHAR(20);   
DECLARE @出勤_日期 INT; 
 
--更新  出勤_上班時(shí)長(zhǎng) 
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; 
--將變量傳入到表中，使取到的值唯一，對(duì)出勤_上班時(shí)長(zhǎng)進(jìn)行更新 
UPDATE 出勤 SET 出勤_上班時(shí)長(zhǎng)= 
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  配置(配置_日期,組_名,組類(lèi)別_名,組_號(hào),組類(lèi)別_號(hào)) 
 SELECT  
 a.出勤_日期,b.組_名,c.組類(lèi)別_名,a.組_號(hào),c.組類(lèi)別_號(hào) 
 FROM 出勤 a 
 JOIN 組 b ON a.組_號(hào) = b.組_號(hào) 
 JOIN 組類(lèi)別 c ON b.組類(lèi)別_號(hào) = c.組類(lèi)別_號(hào) 
 WHERE a.ID=@ID  
 AND  a.出勤_日期=@出勤_日期   
END   
END 

代碼解讀

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

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

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

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

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

測(cè)試功能

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

出勤

我們看到出勤_上班時(shí)長(zhǎng)是沒(méi)有數(shù)據(jù)的，下面我們開(kāi)始更新

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

執(zhí)行完后我們?cè)倏闯銮诒碇械臄?shù)據(jù)是否有變化

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

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

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

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

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

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

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

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

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

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

總結(jié)

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

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