Trigger Help - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Trigger Help

Author

Topic

JohnnyLethal
Starting Member

7 Posts

Posted - 2012-05-02 : 11:48:10

Hi, trying to write a trigger to populate table B anytime time table A is written too. The sql below returns exactly what I need, but the trigger doesn't write anything to the insert table. Any suggestions would be appreciated.

SELECT case when PE.PUNCHEVENTID = T.STARTPUNCHEVENTID then T.STARTPUNCHEVENTID
else T.ENDPUNCHEVENTID end as PUNCHEVENTID
,P.PERSONNUM
,PUNCHDTM
,case when PE.OVERRIDETYPEID = 2 then 1
else case when PE.OVERRIDETYPEID = 4 then 2
else case when PE.PUNCHEVENTID = T.STARTPUNCHEVENTID then 3
else case when PE.PUNCHEVENTID = T.ENDPUNCHEVENTID then 4
end
end
end
end as STATUS
,case when SUBSTRING(C.CLNT,1,3) = 'KVS'
then SUBSTRING(C.CLNT,14,12)
else 'PC Edit' end AS LOCATION
,PUNCHDTM
,''
,PUNCHDTM
,''
FROM PUNCHEVENT PE join TIMESHEETITEM T ON ((PE.PUNCHEVENTID = T.STARTPUNCHEVENTID) OR (PE.PUNCHEVENTID = T.ENDPUNCHEVENTID))
join person p on PE.employeeid = p.PERSONID
join WTKEMPLOYEE E ON PE.EMPLOYEEID = E.EMPLOYEEID
join DATASOURCE D ON PE.DATASOURCEID = D.DATASOURCEID
join CLIENTCONTEXT C ON D.CLIENTCONTEXTID = C.CLIENTCONTEXTID
WHERE E.PAYRULEID = '3'
order by PE.PUNCHDTM desc

--------------- Here' the trigger. I don't get any errors, just no inserted data. Thanks again

CREATE TRIGGER [dbo].[TMW_Punch_Timeclock] ON [dbo].[PUNCHEVENT]
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.PUNCHEVENTID = T.STARTPUNCHEVENTID then T.STARTPUNCHEVENTID
else T.ENDPUNCHEVENTID end as PUNCHEVENTID
,P.PERSONNUM
,PUNCHDTM
,case when inserted.OVERRIDETYPEID = 2 then 1
else case when inserted.OVERRIDETYPEID = 4 then 2
else case when inserted.PUNCHEVENTID = T.STARTPUNCHEVENTID then 3
else case when inserted.PUNCHEVENTID = T.ENDPUNCHEVENTID then 4
end
end
end
end as STATUS
,PUNCHDTM
,case when SUBSTRING(C.CLNT,1,3) = 'KVS'
then SUBSTRING(C.CLNT,14,12)
else 'PC Edit' end AS LOCATION
,PUNCHDTM
,''
,PUNCHDTM
,''
FROM inserted join TIMESHEETITEM T ON ((inserted.PUNCHEVENTID = T.STARTPUNCHEVENTID) OR (inserted.PUNCHEVENTID = T.ENDPUNCHEVENTID))
join person p on inserted.employeeid = p.PERSONID
join WTKEMPLOYEE E ON inserted.EMPLOYEEID = E.EMPLOYEEID
join DATASOURCE D ON inserted.DATASOURCEID = D.DATASOURCEID
join CLIENTCONTEXT C ON D.CLIENTCONTEXTID = C.CLIENTCONTEXTID
WHERE E.PAYRULEID = '3'
order by inserted.PUNCHDTM desc

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-02 : 13:12:23

[code]Hi Johnny

Your are selecting the data by your query .You are not inserting

any records in [dbo].[PUNCHEVENT] table whenever you will insert a record in this table trigger will gets fired .[/code]

JohnnyLethal
Starting Member

7 Posts

Posted - 2012-05-03 : 12:55:23

Thanks for the reply, but I'm still a bit confused.
Every time a record is written into this table: 'PUNCHEVENT' I need another record written to this table: 'kronos_shiftschedules_log'.

So I'm trying to do it with a trigger, the sql returns the right values, but the trigger doesn't write anything into 'kronos_shiftschedules_log'.

I don't know much about triggers, any help would be appreciated.

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-03 : 13:37:10

Can you please let us know the create script of
table [dbo].[PUNCHEVENT]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-03 : 15:05:51

Do the joins and/ore predicate preclude a row fom being selectes? Also, do you need the ORDER BY? seems like unnecessary overhead.

JohnnyLethal
Starting Member

7 Posts

Posted - 2012-05-03 : 15:06:48

Here you, and thanks again for the help:
(btw, are you in India and still looking for a job?)

CREATE TABLE [dbo].[PUNCHEVENT](
[PUNCHEVENTID] [int] NOT NULL,
[EMPLOYEEID] [int] NOT NULL,
[DATASOURCEID] [int] NOT NULL,
[LABORACCTID] [int] NULL,
[WORKRULEID] [int] NULL,
[BREAKRULEID] [int] NULL,
[DEDUCTRULEID] [int] NULL,
[OVERRIDETYPEID] [int] NULL,
[PUNCHDTM] [datetime] NOT NULL,
[TIMEZONEID] [int] NULL,
[CNCLWRKRLTRANSFSW] [int] NOT NULL,
[DELETEDSW] [int] NOT NULL,
[ENTEREDONDTM] [datetime] NOT NULL,
[HASCOMMENTSW] [int] NOT NULL,
[CANCELDEDUCTSW] [int] NOT NULL,
[ORGIDSID] [int] NULL,
[EXCEPTIONRESOLVESW] [int] NULL,
[UPDATEDTM] [datetime] NULL,
CONSTRAINT [PK_PUNCHEVENT] PRIMARY KEY NONCLUSTERED
(
[PUNCHEVENTID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [tkcs3]
) ON [tkcs3]

GO

ALTER TABLE [dbo].[PUNCHEVENT] WITH CHECK ADD CONSTRAINT [FK2_0212] FOREIGN KEY([DATASOURCEID])
REFERENCES [dbo].[DATASOURCE] ([DATASOURCEID])
GO

ALTER TABLE [dbo].[PUNCHEVENT] CHECK CONSTRAINT [FK2_0212]
GO

ALTER TABLE [dbo].[PUNCHEVENT] WITH CHECK ADD CONSTRAINT [FK3_0212] FOREIGN KEY([LABORACCTID])
REFERENCES [dbo].[LABORACCT] ([LABORACCTID])
GO

ALTER TABLE [dbo].[PUNCHEVENT] CHECK CONSTRAINT [FK3_0212]
GO

ALTER TABLE [dbo].[PUNCHEVENT] WITH CHECK ADD CONSTRAINT [FK4_0212] FOREIGN KEY([WORKRULEID])
REFERENCES [dbo].[WORKRULEIDS] ([WORKRULEID])
GO

ALTER TABLE [dbo].[PUNCHEVENT] CHECK CONSTRAINT [FK4_0212]
GO

ALTER TABLE [dbo].[PUNCHEVENT] WITH CHECK ADD CONSTRAINT [FK5_0212] FOREIGN KEY([BREAKRULEID])
REFERENCES [dbo].[BREAKRULE] ([BREAKRULEID])
GO

ALTER TABLE [dbo].[PUNCHEVENT] CHECK CONSTRAINT [FK5_0212]
GO

ALTER TABLE [dbo].[PUNCHEVENT] WITH CHECK ADD CONSTRAINT [FK6_0212] FOREIGN KEY([DEDUCTRULEID])
REFERENCES [dbo].[DEDUCTRULE] ([DEDUCTRULEID])
GO

ALTER TABLE [dbo].[PUNCHEVENT] CHECK CONSTRAINT [FK6_0212]
GO

ALTER TABLE [dbo].[PUNCHEVENT] WITH CHECK ADD CONSTRAINT [FK7_0212] FOREIGN KEY([OVERRIDETYPEID])
REFERENCES [dbo].[OVERRIDETYPE] ([OVERRIDETYPEID])
GO

ALTER TABLE [dbo].[PUNCHEVENT] CHECK CONSTRAINT [FK7_0212]
GO

ALTER TABLE [dbo].[PUNCHEVENT] WITH CHECK ADD CONSTRAINT [FK8_0212] FOREIGN KEY([ORGIDSID])
REFERENCES [dbo].[ORGIDS] ([ORGIDSID])
GO

ALTER TABLE [dbo].[PUNCHEVENT] CHECK CONSTRAINT [FK8_0212]
GO

ALTER TABLE [dbo].[PUNCHEVENT] WITH CHECK ADD CONSTRAINT [FK9_0212] FOREIGN KEY([EMPLOYEEID])
REFERENCES [dbo].[JAIDS] ([EMPLOYEEID])
GO

ALTER TABLE [dbo].[PUNCHEVENT] CHECK CONSTRAINT [FK9_0212]
GO

ALTER TABLE [dbo].[PUNCHEVENT] ADD DEFAULT ((0)) FOR [CNCLWRKRLTRANSFSW]
GO

ALTER TABLE [dbo].[PUNCHEVENT] ADD DEFAULT ((0)) FOR [DELETEDSW]
GO

ALTER TABLE [dbo].[PUNCHEVENT] ADD DEFAULT (getdate()) FOR [ENTEREDONDTM]
GO

ALTER TABLE [dbo].[PUNCHEVENT] ADD DEFAULT ((0)) FOR [HASCOMMENTSW]
GO

ALTER TABLE [dbo].[PUNCHEVENT] ADD DEFAULT ((0)) FOR [CANCELDEDUCTSW]
GO

ALTER TABLE [dbo].[PUNCHEVENT] ADD DEFAULT (getdate()) FOR [UPDATEDTM]
GO

JohnnyLethal
Starting Member

7 Posts

Posted - 2012-05-03 : 15:08:42

Hi and thanks for offering some assistance. I probably left the order by in there from testing the select statement... I'll get rid of that.

I do think it's a problem with my joins... when I keep it simple and don't join the TIMESHEETITEM table, it works fine... but I can't get all my data without that table.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-03 : 15:13:16

I guess my point is that if you just selected from INSERTED and inserted that into a table, you (should) see that each row get selected. Because you are joining to other tables AND you have a WHERE claue it is possible that you are not getting any rows selected because of that. So, the row(s) are in the INSERTED table, but they are getting filtered out by a join or predicate.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources