如何在不获取冗余值的情况下创建触发器？ [英] How can I create a trigger without getting redundant values ?

问题描述

我正在尝试创建一个触发器，允许我在从另一个表插入后保存数据Emp_AttendanceByDevice

我的表Emp_AttendanceByDevice是这样的：

 EnrollNumber InOutMode wWeth wWayth wWay wHour wMinute wSecond 
 27 0 2015 7 9 8 0 0 
 27 2 2015 7 9 10 0 0 
 27 3 2015 7 9 11 0 0 
 27 1 2015 7 9 12 0 0 
 27 0 2015 7 9 13 0 0 
 27 1 2015 7 9 17 0 0 

InOutMode - > 0：CheckIn 1：CheckOut 2：BreakIn 3：BreakOut



我想获得一个触发器保存数据，如下例所示：

 EnrollNumber InOutMode DateAttendance TimesInOut 
 27 0 07-09-2015 07-09-2015 08:00:00 
 27 2 07-09- 2015 07-09-2015 10:00:00 
 27 3 07-09-2015 07-09-2015 11:00:00 
 27 1 07-09-2015 07-09-2015 12： 00:00 
 27 0 07-09-2015 07-09-2015 13:00:00 
 27 1 07-09-2015 07-09-2015 17:00:00 

我尝试了下面的查询，但它给了我多余的价值！

 创建  TRIGGER  [dbo]。 [Trigger_Attendance] 
  ON  [dbo]。[emp_AttendanceByDevice] 
 AFTER  INSERT  
  AS  
  BEGIN  
 
  INSERT   INTO  Log_Attendance（EnrollNumber，DateAttendance，TimesInOut，Mode）
  SELECT  
 emp_AttendanceByDevice.EnrollNumber，
 CAST（强制转换（wYear  as   char （ 4 ））+ '   - ' ++ cast（wMonth  as   varchar （ 2 ））+ '   - ' ++ cast（wDay  as   varchar  （ 2 ）） as   datetime ）， 
 CAST（
 cast（wYear  as   char （ 4 ））+ '   - ' ++ cast（wMonth  as   varchar （ 2 ））+ '   -   ++ cast（wDay  as   varchar （ 2 ））+ ' ' + 
 
 cast（wHour  as   char （ 4 ））+ ' ：' ++ cast（wMinute  as   varchar （ 2 ））+ ' ：' ++ cast（wSecond  as   varchar （ 2 ）） as   datetime ），
 
 emp_AttendanceByDevice.InOutMode 
  FROM  emp_AttendanceByDevice 
  END  

有什么解决方案吗？

解决方案

而不是选择要从实际表中插入的数据使用特殊的插入和已删除的表。



有关详细信息，请参阅使用ins删除和删除表 [ ^

I'm trying to create a trigger which allow me to save data after insert from another table "Emp_AttendanceByDevice"
My table Emp_AttendanceByDevice is like this:

EnrollNumber    InOutMode   wYear   wMonth   wDay    wHour   wMinute   wSecond
27                0          2015     7       9        8       0          0
27                2          2015     7       9        10      0          0
27                3          2015     7       9        11      0          0
27                1          2015     7       9        12      0          0
27                0          2015     7       9        13      0          0
27                1          2015     7       9        17      0          0  

InOutMode--> 0: CheckIn 1: CheckOut 2: BreakIn 3: BreakOut

I want to get a trigger save data like the example below:

EnrollNumber       InOutMode       DateAttendance       TimesInOut
27                   0              07-09-2015           07-09-2015 08:00:00
27                   2              07-09-2015           07-09-2015 10:00:00
27                   3              07-09-2015           07-09-2015 11:00:00
27                   1              07-09-2015           07-09-2015 12:00:00
27                   0              07-09-2015           07-09-2015 13:00:00
27                   1              07-09-2015           07-09-2015 17:00:00

I tried the query below but It gives me redundant values!

Create TRIGGER [dbo].[Trigger_Attendance] 
   ON  [dbo].[emp_AttendanceByDevice] 
   AFTER INSERT
AS 
BEGIN
   
      INSERT INTO Log_Attendance(EnrollNumber, DateAttendance,TimesInOut,Mode)
      SELECT 
       emp_AttendanceByDevice.EnrollNumber,
         CAST(	cast(wYear as char(4))+'-'++cast(wMonth as varchar(2))+'-'++cast(wDay as varchar(2)) as datetime),
       CAST(	
         cast(wYear as char(4))+'-'++cast(wMonth as varchar(2))+'-'++cast(wDay as varchar(2))+' '+
         
         cast(wHour as char(4))+':'++cast(wMinute as varchar(2))+':'++cast(wSecond as varchar(2)) as datetime),

 emp_AttendanceByDevice.InOutMode
      FROM emp_AttendanceByDevice
END

Any solution please?

解决方案

Instead of selecting the data to insert from the actual table use special inserted and deleted tables.

For more information, see Use the inserted and deleted Tables[^]

这篇关于如何在不获取冗余值的情况下创建触发器？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！