如何在不获取冗余值的情况下创建触发器? [英] 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 specialinserted
anddeleted
tables.
For more information, see Use the inserted and deleted Tables[^]
这篇关于如何在不获取冗余值的情况下创建触发器?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文