带条件插入时的SQL触发器 [英] SQL trigger on insert with condition

问题描述

你好，

我对插入后如何使用触发器感到困惑(因为我是开发人员，这是我的第一个触发器)
我们安装了与当前小时考勤系统不兼容的新考勤机，因此我不得不创建一个名为New_Attendance的新表，我们当前的系统从Attendance_LOG中读取数据.因此，尽管我在表New_Attendance上创建了一个触发器，但是我需要做的是为什么我感到困惑，旧的系统用来创建然后更新记录以进行检出，新的机器正在为IN和OUT创建单独的记录，所以请在这里指导我如何操作

桌子
*出席记录:
-ENTNO
-EMP_NO
-Date_IN datetime
-Date-OUT日期时间
-等...

* New_Attendace
-EMP_NO
-日期

我的IDEA(明智的编码方式)

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)

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 ...)}

首先我的想法是正确的还是需要解决?
而且我知道我的sql脚本有很多错误，所以请帮助我纠正它们

谢谢

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

推荐答案

我做到了，这里的解决方案(肯定是新手解决方案)

-我声明了我需要的所有变量

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

- i declared all the variables i need

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

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

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

这篇关于带条件插入时的SQL触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！