Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JohnnyLethal
Starting Member
7 Posts |
 Posted - 2012-05-08 : 14:27:00
|
| Thanks in advance for the help. I'm new with triggers and struggling with this one. It should pick up punches from a timeclock and write them to another table. The trigger works great for the IN punches, but ignores the OUT punches and doesn't write them out. Here's the trigger, when just the select statement is run, it returns both IN and OUT punches:CREATE TRIGGER [dbo].[TMW_Punch_Timeclock] ON [dbo].[TIMESHEETITEM]FOR INSERT AS INSERT INTO [dbo].[kronos_shiftschedules_log](ssl_id,ssl_employeeid,ssl_activitydate,ssl_auditreason,ssl_createdate,ssl_createdby,ssl_lastupdatedate,ssl_lastupdateby,ssl_puncheventid,ssl_status) SELECT case when inserted.STARTPUNCHEVENTID = I.PUNCHEVENTID then inserted.STARTPUNCHEVENTID else inserted.ENDPUNCHEVENTID end,P.PERSONNUM,I.PUNCHDTM,case when inserted.STARTPUNCHEVENTID = I.PUNCHEVENTID then 'LI' else 'LO' end,I.PUNCHDTM,SUBSTRING(C.CLNT,14,12),I.PUNCHDTM,'',case when inserted.STARTPUNCHEVENTID = I.PUNCHEVENTID then inserted.STARTPUNCHEVENTID else inserted.ENDPUNCHEVENTID end,''FROM inserted join person p on inserted.employeeid = p.PERSONIDjoin WTKEMPLOYEE E ON inserted.EMPLOYEEID = E.EMPLOYEEIDjoin VP_PUNCHITEM I ON (inserted.STARTPUNCHEVENTID = I.PUNCHEVENTID or inserted.ENDPUNCHEVENTID = I.PUNCHEVENTID)join DATASOURCE D ON (I.PUNCHDATASOURCEID = D.DATASOURCEID)join CLIENTCONTEXT C ON (D.CLIENTCONTEXTID = C.CLIENTCONTEXTID)WHERE E.PAYRULEID = '3'AND I.PUNCHOVERRIDE IS NULL |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
JohnnyLethal
Starting Member
7 Posts |
Posted - 2012-05-08 : 14:45:55
|
| Certianly, but can you help me decide what to post? Would you like to see the data from main table the trigger sits on? or are you looking for a desc on the destination table?Thanks |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
JohnnyLethal
Starting Member
7 Posts |
Posted - 2012-05-08 : 17:10:42
|
| Hi everyone, I went in another direction to solve my need. Thanks for all the help and I still hope to get better with triggers. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Trigger Troubleshooting
Tara Kizer