本文转载自微信公众号《SQL数据库开发》，作者，平凡的世界。转载本文请联系SQL数据库开发公众号。最近有朋友找我帮忙写一个触发器。看了一下需求，很明确，就是插入后执行更新，触发一些事件。我觉得很有意思，就写给他看，在这里分享给大家。表结构有以下四张表：考勤组类别配置问题1.更新[attendance_workhours]如果：“attendance”表，[attendance_worktime]或[attendance_off-dutytime]，列变化触发的事件Update上面“考勤”表两列，attendance_workhours=attendance_offhours-attendance_workhours插入上面两列“考勤”表，attendance_workhours不插入数据，插入完成后计算。attendance_officetime=attendance_offworktime-attendance_officetime2.Insertif:"attendance"table,[attendance_date],插入列变化触发的事件(configuration_date,group_name,groupcategory_name,group_number,groupcategory_number)query[A。attendance_date，b。组名，c。group_category_name，一个。group_number，c。group_category_number]根据给定的创建表结构我们在数据库中创建表结构/*时间：2021-01-25作者：Lyven需求：创建触发器完成相应的更新和插入功能*/使用SQL_RoadCREATETABLEattendance(IDINTIDENTITY(1,1)NOTNULLPRIMARYKEY,attendance_月INT,attendance_dateINT,attendance_work时间VARCHAR(20),attendance_下班时间VARCHAR(20),attendance_work时间VARCHAR(20),group_numberVARCHAR(10))CREATETABLEgroup(IDINTIDENTITY(1,1)NOTNULLPRIMARYKEY,group_numberVARCHAR(10),group_nameNVARCHAR(20),groupcategory_numberVARCHAR(10),group_numberofpeopleINT)CREATETABLEgroupcategory(IDINTIDENTITY(1,1)NOTNULLPRIMARYKEY,groupcategory_numberVARCHAR(10),groupcategory_name组类别NVARCHAR(20),groupcategory_hourlysalaryNUMERIC(18,2))CREATETABLEconfiguration(IDINTIDENTITY(1,1)NOTNULLPRIMARYKEY,configuration_dateINT,group_nameVARCHAR(20),Groupcategory_nameNVARCHAR(20),配置_hoursVARCHAR(20),configuration_salaryNUMERIC(18,2),group_numberVARCHAR(10),groupcategory_numberVARCHAR(10))GO插入测试数据INSERTINTOattendance(Attendance_month,attendance_date,attendance_workingtime,attendance_off-dutytime,group_number)VALUES(12,24,'7:30','12:35','01'),(12,25,'8:00','12:28','01'),(12,26,'8:30','12:00','01')INSERTINTOgroup(group_number,group_name,Groupcategory_number,group_numberofpeople)VALUES('01','CAD','01',2),('02','MAX','02',1),('03','U3D','03',3)INSERTINTOgroupcategory(groupcategory_number,groupcategory_name,groupcategory_hourlysalary)VALUES('01',N'automatic',100.00),('02',N'员工',200.00),('03',N'student',150.00)插入配置（configuration_date,group_name,groupcategory_name,configuration_salary,group_number,groupcategory_number）VALUES(24,'CAD',N'Auto',12.50,'01','01'),(25,'MAX',N'Employee',12.60,'02','02'),(26,'U3D',N'student',12.70,'03','03')需求分析第一个需求其实是只要上班时间和下班时间，我们会自动为它计算时间长度，其实这样的需求在插入的时候就可以解决，这里不讨论这个优化方案，看看这个trigger是怎么按照这个要求写的吧。第二个需求是当日期发生变化时，我们需要在配置表中插入一条数据，这样我们就可以把这两个需求写在一个触发器中。测试代码CREATETRIGGERT_attendance--CreatetriggerONattendanceAFTERUPDATE,INSERT--一个trigger可以同时写updateinsert和delete动作ASBEGIN--定义变量DECLARE@IDINT;DECLARE@attendance_worktimeVARCHAR(20);DECLARE@attendance_offwork时间VARCHAR(20);DECLARE@attendance_dateINT;--updateattendance_worktimeIF(UPDATE(attend_worktime)ORUPDATE(attend_offworktime))--如果attendance_work时间与考勤_下班时间发生更新动作时，执行如下代码BEGIN--先获取更新值保存在变量中，其中inserted表是存储更新值的系统表SELECT@ID=ID，@attendance_workingtime=Attendance_working时间，@attendance_off-dutytime=attendance_off-duty时间FROMinserted；--将变量输入表中，使得到的值唯一，updateattendance_workingtimeUPDATEattendanceSETattendance_workingtime=CONVERT(varchar(100),DATEADD(ss,DATEDIFF(ss,attendance_workingtime,attendance_off-dutytime),0),108)WHEREID=@IDAND(attendance_workingtime=@attendance_workingtimeORattendance_off-dutytime=@attendance_off-dutytime);END--插入配置信息IFUPDATE(attendance_date)--当attendance_date发生变化时，我们进行如下更新。BEGIN--获取更新后的值并传递给变量SELECT@ID=ID,@attendance_date=attendance_dateFROMinserted;--执行插入操作INSERTINTO配置(configuration_date,group_name,groupcategory_name,group_No.,groupcategory_number)SELECTa.attendance_date,b.group_name,c.group_category_name,a.group_number,c.group_category_numberFROMattendanceaJOINgroupbONa.group_number=b.group_numberJOIN群类别cONb.groupcategory_number=c.groupcategory_numberWHEREa.ID=@IDANDa.attend_date=@attend_dateENDEND代码解读1.触发器的语法一定要掌握。本例是在SQLServer下执行的，其他关系型数据库的语法可能有所不同，请注意。2.触发器中可以实现多种不同的操作，update,delete,insert都可以写在触发器上，当然要视情况而定3.触发器执行的时候，update前的数据会被存储在临时表中在删除后，更新后，数据将存储在插入的临时表中。这里我们使用临时表inserted4。更新工作时间时，使用时间处理函数DATEDIFF和DATEADD。这两个函数是比较常用的时间处理函数。必须掌握。5.参数传递是代码的重要部分。我们先把临时表中的数据存储在一个变量中，然后把这个变量取出来，在我们真正进行update或者insert操作的时候使用，也就是把这个变量再传给条件语句。测试功能1、测试数据之前，我们先看一下考勤表和配置表中的数据。attendance我们看到attendance_workinghours没有数据，那么开始更新updateattendanceSETAttendance_Workinghours='7:00'WHEREID=1'执行后，看attendance表的数据有没有变化.从上图可以看出，结果符合我们的预期。同样，更新下班时间也会更新工作时间，这里就不演示了。2、插入数据时是否也会更新工作时间？我们执行下面的语句INSERTINTOattendance(attendance_month,attendance_date,attendance_workingtime,attendance_off-dutytime,group_number)VALUES(12,11,'8:30','12:00','01')执行完我们来查看结果和结果也符合我们的预期。3、当考勤表中的日期更新时，是否会在配置表中插入一条数据？我们先看配置表中的数据，更新考勤表中的日期，看看配置表中会不会多了一条记录？更新Edbo。AttendanceSETAttendance_Date='22'WHEREID=2更新后，我们查看配置表中的数据，结果也符合我们的预期。总结整个案例，本质部分其实就是触发部分，但是为了让小伙伴们阅读和思考的更清楚，所以从需求到测试展示了整个案例。至于触发器部分，如果你对它的语法有更好的理解，它并没有你想象的那么难用。当然，还有一些技巧你还是需要了解的，比如可以在一个触发器中编写更新和插入操作。我们也可以指定，只有在哪些列发生更新操作时，才会执行相应的语句。最后，如果你有一些更经典的要求，也可以发给我，说不定下次我会展示你的案例！
