鲁达 最近有小伙伴向我请求帮助，要写一个触发器。我看了一下需求很明确，就是执行更新，插入后触发一些事件。觉得挺有意思的，于是帮他写了一下，这里分享给大家。
任务需求
有如下四张表：
出勤
组
组类别
配置
1.更新[出勤_上班时长] 如果:"出勤"表,[出勤_上班时间]或者[出勤_下班时间],列发生改变所触发事件
- 更新上述两列 "出勤"表,出勤_上班时长 = 出勤_下班时间 - 出勤_上班时间
- 插入上述两列 "出勤"表,出勤_上班时长不插数据，插入完成后计算它。出勤_上班时长 = 出勤_下班时间 - 出勤_上班时间
2.插入 如果:"出勤"表,[出勤_日期],列发生改变所触发事件
插入 (配置_日期,组_名,组类别_名,组_号,组类别_号)
查询[a.出勤_日期,b.组_名,c.组类别_名,a.组_号,c.组类别_号]
创建表结构
根据给定的表结构，我们创建到数据库中
/* 时间：2018-12-26 作者：Lyven 需求：创建一个触发器，完成相应的更新和插入功能 */ 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' )分析需求
- 第一个需求其实是只要上班时间和下班时间，我们就自动给它算出这个时长，其实这样的需求在插入的时候就可以解决，这里我们不讨论这种优化方案，只是根据这个需求看该如何写出这个触发器。
- 第二个需求则是在日期发生变动的时候，需要对配置表插入一条数据
这样我们可以把这两个需求写在一个触发器当中。
需求代码
CREATE TRIGGER T_出勤 --创建 触发器 ON 出勤 AFTER UPDATE,INSERT --一个触发器可以同时写更新插入和删除等动作 AS BEGIN --定义变量 DECLARE @ID INT; DECLARE @出勤_上班时间 VARCHAR(20); DECLARE @出勤_下班时间 VARCHAR(20); DECLARE @出勤_日期 INT; --更新 出勤_上班时长 IF (UPDATE (出勤_上班时间) OR UPDATE (出勤_下班时间) ) --如果出勤_上班时间和出勤_下班时间发生了更新动作，则执行如下代码 BEGIN --先获取更新后的值保留在变量中，其中inserted表为系统表，存放更新后的值 SELECT @ID=ID, @出勤_上班时间=出勤_上班时间, @出勤_下班时间=出勤_下班时间 FROM inserted; --将变量传入到表中，使取到的值唯一，对出勤_上班时长进行更新 UPDATE 出勤 SET 出勤_上班时长= CONVERT(varchar(100) , DATEADD(ss, DATEDIFF(ss, 出勤_上班时间, 出勤_下班时间), 0), 108) WHERE ID=@ID AND (出勤_上班时间=@出勤_上班时间 OR 出勤_下班时间=@出勤_下班时间); END --插入配置信息 IF UPDATE (出勤_日期) --当出勤_日期发生了变动，我们执行如下更新。 BEGIN --获取更新后的值传给变量 SELECT @ID=ID , @出勤_日期=出勤_日期 FROM inserted; --执行插入操作 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、触发器的语法这个必须掌握，本案例是在SQL Server下执行的，其他关系数据库的语法可能不同，请注意一下。
2、触发器中可以实现多种不同的操作，更新，删除，插入均可写在一个触发器上，当然要视情况而定
3、触发器在执行时会将更新前的数据存放在临时表deleted中，在更新后会将数据存放在临时表inserted中，这里我们就用到了临时表inserted
4、在更新上班时长时用到了时间处理函数DATEDIFF和DATEADD，两个函数是比较常用的时间处理函数，必须掌握。
5、参数传递是代码中比较重要一环，我们是先将临时表中的数据存放在一个变量中保存，在我们真正进行更新或插入操作时候再把这个变量取出来使用，就是将变量再次传递给条件语句。
测试功能
1、在测试数据之前，我们先看看出勤表和配置表中的数据
出勤
我们看到出勤_上班时长是没有数据的，下面我们开始更新
UPDATE 出勤 SET 出勤_上班时间='7:00' WHERE ID=1执行完后我们再看出勤表中的数据是否有变化
从上图可以看出，结果符合我们预期，同理更新下班时间也会对上班时长进行更新操作，这里就不演示了。
2、我们插入数据是否也会更新上班时长呢？我们执行如下语句
INSERT INTO 出勤(出勤_月份,出勤_日期, 出勤_上班时间,出勤_下班时间,组_号) VALUES (12,11,'8:30','12:00','01')执行完后我们查看一下结果
结果也符合我们的预期。
3、当出勤表中的日期被更新的时候，配置表里是否会插入了一条数据？我们先看看配置表中的数据
我们对出勤表中的日期进行更新操作，看配置表会不会多一条记录？
UPDATE dbo.出勤 SET 出勤_日期='22' WHERE ID=2更新后我们看看配置表中的数据
结果也符合我们的预期。
总结
整个案例其实精华部分就只是触发器部分，但是为了让小伙伴们能更加清晰的阅读和思考，故将整个案例从需求到测试都给大家展现出来。而触发器部分如果对其语法比较了解，使用起来并没有想象的那么难。当然其中有一些小技巧还是需要大家去了解一下，就比如将更新和插入操作写在一个触发器是可以的。我们也可以指定只有哪几列发生更新操作的时候才执行相应的语句。最后，如果你有一些比较经典的需求，也可以发送给我，兴许下次展示的就是你的案例啦！