[[415489]]

本文轉載自微信公眾號「SQL數據庫開發(fā)」，作者丶平凡世界。轉載本文請聯(lián)系SQL數據庫開發(fā)公眾號。

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

表結構

有如下四張表：

出勤

組

組類別

配置

問題

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

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

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

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

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

創(chuàng)建表結構

根據給定的表結構，我們創(chuàng)建到數據庫中

/* 
時間：2021-01-25 
作者：Lyven 
需求：創(chuàng)建一個觸發(fā)器，完成相應的更新和插入功能 
*/ 
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), 
組_人數 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 

插入測試數據

INSERT INTO 出勤(出勤_月份,出勤_日期,出勤_上班時間,出勤_下班時間,組_號) 
VALUES 
( 12, 24, '7:30', '12:35', '01' ), 
( 12, 25, '8:00', '12:28', '01' ), 
( 12, 26, '8:30', '12:00', '01' ) 
 
INSERT INTO 組(組_號,組_名,組類別_號,組_人數) 
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'學員', 150.00 ) 
 
INSERT INTO 配置(配置_日期 , 組_名, 組類別_名, 配置_工資 , 
組_號, 組類別_號) 
VALUES 
( 24, 'CAD', N'自動', 12.50, '01', '01' ), 
( 25, 'MAX', N'員工', 12.60, '02', '02' ), 
( 26, 'U3D', N'學員', 12.70, '03', '03' ) 

需求分析

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

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

測試代碼

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; 
--將變量傳入到表中，使取到的值唯一，對出勤_上班時長進行更新 
UPDATE 出勤 SET 出勤_上班時長= 
CONVERT(varchar(100) , DATEADD(ss, DATEDIFF(ss, 出勤_上班時間, 出勤_下班時間), 0), 108) 
WHERE ID=@ID  
AND (出勤_上班時間=@出勤_上班時間 
OR 出勤_下班時間=@出勤_下班時間); 
END 
 
--插入配置信息 
IF UPDATE (出勤_日期) 
--當出勤_日期發(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í)行的，其他關系數據庫的語法可能不同，請注意一下。

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

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

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

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

測試功能

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

出勤

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

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

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

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

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

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

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

結果也符合我們的預期。

3、當出勤表中的日期被更新的時候，配置表里是否會插入了一條數據?我們先看看配置表中的數據

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

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

更新后我們看看配置表中的數據

結果也符合我們的預期。

總結

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

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