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?