SQL trigger on insert with condition

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

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 26-Feb-14 19:42pm

Hakmeh Mohannad

Updated 26-Feb-14 23:19pm

Maciej Los

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Hakmeh Mohannad · Accepted Answer · 2014-02-26T23:42:00

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