Click here to Skip to main content
14,981,116 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hello,

I'm confused on how using the trigger after insert (because i'm developer & this my first trigger)
we installed new attendance machine which are not compatible with our current hr attendance system so i had to create new table called New_Attendance, our current system reads from Attendance_LOG. So i though of creating a trigger on my table New_Attendance but what i need to do is why i'm confused, the old system used to create then update the record for checking out, the new machine is creating seprate records for IN and OUT, so please guide me here on how i should do it

The tables
* Attendance_LOG:
- ENTNO
- EMP_NO
- Date_IN datetime
- Date-OUT datetime
- etc ...

* New_Attendace
- EMP_NO
- Date

My IDEA (coding wise)
SQL
SELECT TOP 1 ENTNO,Date_IN FROM Attendance_LOG WHERE Attendance_LOG.EMP_NO = INSERTED.EMP_NO 
ORDER BY Attendance_LOG.ENTNO DESC

IF Date_IN.Date == Today.Date
{
Update Attendace_LOG SET Date_OUT = INSERTED.Date WHERE ENTNO = (SELECT TOP 1 ENTNO FROM Attendance_LOG WHERE Attendance_LOG.EMP_NO = INSERTED.EMP_NO
ORDER BY Attendance_LOG.ENTNO DESC)
}
ELSE
{INSERT INTO Attendance_LOG VALUE(1,2, etc ...)}


first of all is my idea correct or does it need fixing?
and i know there is alot of mistakes with my sql script so please help me correcting them

thank you
Posted
Updated 26-Feb-14 23:19pm
v2

1 solution

i did it and here the solution (newbies solution for sure)

- i declared all the variables i need
SQL
DECLARE @OLD_ENTNO decimal(18,0)
DECLARE @OLD_IN varchar(10)

DECLARE @NEW_IN_TIME datetime
DECLARE @NEW_IN varchar(10)
DECLARE @USERID varchar(10)
DECLARE @DEPTID varchar(10)
DECLARE @BRNCHID varchar(10)


- filled the variables
SQL
SET @OLD_ENTNO = (SELECT TOP 1 ENTNO FROM CLOCKING WHERE EMP_NO = (SELECT USERID FROM INSERTED) ORDER BY DATE_TIME_IN DESC)
SET @OLD_IN = CONVERT(varchar(10),(SELECT DATE_TIME_IN FROM CLOCKING WHERE ENTNO = @OLD_ENTNO),120)

SET @NEW_IN_TIME = (SELECT PROCESSTIME2 FROM INSERTED)
SET @NEW_IN = CONVERT(varchar(10), (SELECT PROCESSTIME2 FROM INSERTED),120)
SET @USERID = CONVERT(varchar(10), (SELECT USERID FROM INSERTED),120)
SET @DEPTID = '0'+ CONVERT(varchar(10), (SELECT DEPARTMENTID FROM INSERTED),120)
SET @BRNCHID = '0'+ SUBSTRING(@DEPTID,1,1)


- used if condition
SQL
IF (@OLD_IN = @NEW_IN)
BEGIN
UPDATE CLOCKING SET DATE_TIME_OUT = (SELECT PROCESSTIME2 FROM INSERTED), STATUS = 'OUT' WHERE ENTNO = @OLD_ENTNO
END

IF (@OLD_IN < @NEW_IN)
BEGIN
INSERT INTO CLOCKING (EMP_NO,BRANCH_NO,DEPT_NO,DATE_TIME_IN,STATUS) VALUES (@USERID,@BRNCHID,@DEPTID,@NEW_IN_TIME,'IN')
END
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900