看一下SQL触发器实战

时间:2021-10-10 02:26:57

看一下SQL触发器实战

最近有小伙伴向我请求帮助,要写一个触发器。我看了一下需求很明确,就是执行更新,插入后触发一些事件。觉得挺有意思的,于是帮他写了一下,这里分享给大家。

表结构

有如下四张表:

出勤

看一下SQL触发器实战

看一下SQL触发器实战

组类别

看一下SQL触发器实战

配置

看一下SQL触发器实战

问题

1.更新[出勤_上班时长] 如果:"出勤"表,[出勤_上班时间]或者[出勤_下班时间],列发生改变所触发事件

  • 更新上述两列 "出勤"表,出勤_上班时长 = 出勤_下班时间 - 出勤_上班时间
  • 插入上述两列 "出勤"表,出勤_上班时长不插数据,插入完成后计算它。出勤_上班时长 = 出勤_下班时间 - 出勤_上班时间

2.插入 如果:"出勤"表,[出勤_日期],列发生改变所触发事件

插入 (配置_日期,组_名,组类别_名,组_号,组类别_号)

查询[a.出勤_日期,b.组_名,c.组类别_名,a.组_号,c.组类别_号]

创建表结构

根据给定的表结构,我们创建到数据库中

  1. /* 
  2. 时间:2021-01-25 
  3. 作者:Lyven 
  4. 需求:创建一个触发器,完成相应的更新和插入功能 
  5. */ 
  6. Use SQL_Road 
  7. CREATE TABLE 出勤 
  8. (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
  9. 出勤_月份 INT , 
  10. 出勤_日期 INT , 
  11. 出勤_上班时间 VARCHAR(20), 
  12. 出勤_下班时间 VARCHAR(20), 
  13. 出勤_上班时长 VARCHAR(20), 
  14. 组_号 VARCHAR(10) 
  15.  
  16. CREATE TABLE 组 
  17. (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
  18. 组_号 VARCHAR(10), 
  19. 组_名 NVARCHAR(20), 
  20. 组类别_号 VARCHAR(10), 
  21. 组_人数 INT 
  22.  
  23. CREATE TABLE 组类别 
  24. (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
  25. 组类别_号 VARCHAR(10), 
  26. 组类别_名 NVARCHAR(20), 
  27. 组类别_时薪 NUMERIC(18,2) 
  28.  
  29. CREATE TABLE 配置 
  30. (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
  31. 配置_日期 INT
  32. 组_名 VARCHAR(20), 
  33. 组类别_名 NVARCHAR(20), 
  34. 配置_工时 VARCHAR(20), 
  35. 配置_工资 NUMERIC(18,2), 
  36. 组_号 VARCHAR(10), 
  37. 组类别_号 VARCHAR(10) 
  38. GO 

插入测试数据

  1. INSERT INTO 出勤(出勤_月份,出勤_日期,出勤_上班时间,出勤_下班时间,组_号) 
  2. VALUES 
  3. ( 12, 24, '7:30''12:35''01' ), 
  4. ( 12, 25, '8:00''12:28''01' ), 
  5. ( 12, 26, '8:30''12:00''01' ) 
  6.  
  7. INSERT INTO 组(组_号,组_名,组类别_号,组_人数) 
  8. VALUES 
  9. '01''CAD''01', 2 ), 
  10. '02''MAX''02', 1 ), 
  11. '03''U3D''03', 3 ) 
  12.  
  13. INSERT INTO 组类别(组类别_号,组类别_名,组类别_时薪) 
  14. VALUES 
  15. '01', N'自动', 100.00 ), 
  16. '02', N'员工', 200.00 ), 
  17. '03', N'学员', 150.00 ) 
  18.  
  19. INSERT INTO 配置(配置_日期 , 组_名, 组类别_名, 配置_工资 , 
  20. 组_号, 组类别_号) 
  21. VALUES 
  22. ( 24, 'CAD', N'自动', 12.50, '01''01' ), 
  23. ( 25, 'MAX', N'员工', 12.60, '02''02' ), 
  24. ( 26, 'U3D', N'学员', 12.70, '03''03' ) 

需求分析

  1. 第一个需求其实是只要上班时间和下班时间,我们就自动给它算出这个时长,其实这样的需求在插入的时候就可以解决,这里我们不讨论这种优化方案,只是根据这个需求看该如何写出这个触发器。
  2. 第二个需求则是在日期发生变动的时候,需要对配置表插入一条数据

这样我们可以把这两个需求写在一个触发器当中。

测试代码

  1. CREATE TRIGGER T_出勤  --创建 触发器 
  2. ON 出勤 
  3. AFTER UPDATE,INSERT   
  4. --一个触发器可以同时写更新插入和删除等动作 
  5. AS 
  6. BEGIN 
  7. --定义变量 
  8. DECLARE @ID INT
  9. DECLARE @出勤_上班时间 VARCHAR(20); 
  10. DECLARE @出勤_下班时间 VARCHAR(20);   
  11. DECLARE @出勤_日期 INT
  12.  
  13. --更新  出勤_上班时长 
  14. IF (UPDATE (出勤_上班时间) OR UPDATE (出勤_下班时间) ) 
  15. --如果出勤_上班时间和出勤_下班时间发生了更新动作,则执行如下代码 
  16. BEGIN 
  17. --先获取更新后的值保留在变量中,其中inserted表为系统表,存放更新后的值 
  18.  SELECT 
  19.  @ID=ID, 
  20.  @出勤_上班时间=出勤_上班时间, 
  21.  @出勤_下班时间=出勤_下班时间 
  22.  FROM inserted; 
  23. --将变量传入到表中,使取到的值唯一,对出勤_上班时长进行更新 
  24. UPDATE 出勤 SET 出勤_上班时长= 
  25. CONVERT(varchar(100) , DATEADD(ss, DATEDIFF(ss, 出勤_上班时间, 出勤_下班时间), 0), 108) 
  26. WHERE ID=@ID  
  27. AND (出勤_上班时间=@出勤_上班时间 
  28. OR 出勤_下班时间=@出勤_下班时间); 
  29. END 
  30.  
  31. --插入配置信息 
  32. IF UPDATE (出勤_日期) 
  33. --当出勤_日期发生了变动,我们执行如下更新。 
  34. BEGIN 
  35. --获取更新后的值传给变量 
  36.  SELECT  
  37.  @ID=ID , 
  38.  @出勤_日期=出勤_日期 
  39.  FROM inserted; 
  40.  --执行插入操作 
  41. INSERT INTO  配置(配置_日期,组_名,组类别_名,组_号,组类别_号) 
  42.  SELECT  
  43.  a.出勤_日期,b.组_名,c.组类别_名,a.组_号,c.组类别_号 
  44.  FROM 出勤 a 
  45.  JOIN 组 b ON a.组_号 = b.组_号 
  46.  JOIN 组类别 c ON b.组类别_号 = c.组类别_号 
  47.  WHERE a.ID=@ID  
  48.  AND  a.出勤_日期=@出勤_日期   
  49. END   
  50. END 

代码解读

1、触发器的语法这个必须掌握,本案例是在SQL Server下执行的,其他关系数据库的语法可能不同,请注意一下。

2、触发器中可以实现多种不同的操作,更新,删除,插入均可写在一个触发器上,当然要视情况而定

3、触发器在执行时会将更新前的数据存放在临时表deleted中,在更新后会将数据存放在临时表inserted中,这里我们就用到了临时表inserted

4、在更新上班时长时用到了时间处理函数DATEDIFF和DATEADD,两个函数是比较常用的时间处理函数,必须掌握。

5、参数传递是代码中比较重要一环,我们是先将临时表中的数据存放在一个变量中保存,在我们真正进行更新或插入操作时候再把这个变量取出来使用,就是将变量再次传递给条件语句。

测试功能

1、在测试数据之前,我们先看看出勤表和配置表中的数据

出勤

看一下SQL触发器实战

我们看到出勤_上班时长是没有数据的,下面我们开始更新

  1. UPDATE 出勤 SET 出勤_上班时间='7:00' 
  2. WHERE ID=1 

执行完后我们再看出勤表中的数据是否有变化

看一下SQL触发器实战

从上图可以看出,结果符合我们预期,同理更新下班时间也会对上班时长进行更新操作,这里就不演示了。

2、我们插入数据是否也会更新上班时长呢?我们执行如下语句

  1. INSERT INTO 出勤(出勤_月份,出勤_日期, 
  2. 出勤_上班时间,出勤_下班时间,组_号) 
  3. VALUES (12,11,'8:30','12:00','01'

执行完后我们查看一下结果

看一下SQL触发器实战

结果也符合我们的预期。

3、当出勤表中的日期被更新的时候,配置表里是否会插入了一条数据?我们先看看配置表中的数据

看一下SQL触发器实战

我们对出勤表中的日期进行更新操作,看配置表会不会多一条记录?

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

更新后我们看看配置表中的数据

看一下SQL触发器实战

结果也符合我们的预期。

总结

整个案例其实精华部分就只是触发器部分,但是为了让小伙伴们能更加清晰的阅读和思考,故将整个案例从需求到测试都给大家展现出来。而触发器部分如果对其语法比较了解,使用起来并没有想象的那么难。

当然其中有一些小技巧还是需要大家去了解一下,就比如将更新和插入操作写在一个触发器是可以的。我们也可以指定只有哪几列发生更新操作的时候才执行相应的语句。最后,如果你有一些比较经典的需求,也可以发送给我,兴许下次展示的就是你的案例啦!

原文链接:https://mp.weixin.qq.com/s/nirFD8HlsgODCTB1d3JezA