Trigger Select Lookup Value - 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 2008 Forums

SQL Server Administration (2008)

Trigger Select Lookup Value

Author

Topic

drawlings
Starting Member

14 Posts

Posted - 2013-03-27 : 07:55:35

I am starting to get the hang of these Triggers now :)

I would like to add the TimeSheetId field from previous record

This is my trigger that inserts the new record into another table from the timesheet table

ALTER TRIGGER [dbo].[Insert_Punch] ON [dbo].[TIMESHEETITEM]
FOR INSERT
AS
BEGIN

SET NOCOUNT ON;

insert A_Table(TimeSheetId,StartPunchId, Startdtm,Name,Flag, Ignore,duration,complete,employeeid,PersonNum)
select TimeSheetItemID,STARTPUNCHEVENTID,StartDTM,p.FullNM,0,0,0,0,employeeid,p.PersonNum from inserted i
left join Person p on p.PersonID = i.EmployeeID
END

In A_Table I created a new field called 'PreviousTimeSheetId' and the below SQL Query will give me the id I require. But I don't know how the alter the insert trigger (above) so that I can get this id value

SELECT TOP 1 TIMESHEETITEMID
  FROM TIMESHEETITEM where EMPLOYEEID = '600' AND STARTPUNCHEVENTID Is Not Null And ENDPUNCHEVENTID is not null order by Startdtm desc

** EmployeeId = '600' needs to be employeeid = i.EmployeeID (I think)

Well hope this makes sense :)

My Free .NET Controls at www.qiosdevsuite.com Includes 30 Controls, Ribbon Toolbar, Ribbon Form etc...

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-27 : 08:31:53

[code]DECLARE @PrevTimeSheetId INT
SELECT TOP 1 @PrevTimeSheetId = TIMESHEETITEMID
FROM TIMESHEETITEM t
JOIN inserted i ON t.EMPLOYEEID = i.EMPLOYEEID
WHERE STARTPUNCHEVENTID Is Not Null And ENDPUNCHEVENTID is not null order by Startdtm desc
[/code]
then use this local variable to insert into table

drawlings
Starting Member

14 Posts

Posted - 2013-03-27 : 08:53:16

Thank you, it seems to be giving me a error

Msg 4104, Level 16, State 1, Procedure Insert_Punch, Line 12
The multi-part identifier "dbo.TIMESHEETITEM.STARTPUNCHEVENTID" could not be bound.
Msg 4104, Level 16, State 1, Procedure Insert_Punch, Line 13
The multi-part identifier "dbo.TIMESHEETITEM.ENDPUNCHEVENTID" could not be bound.
Msg 4104, Level 16, State 1, Procedure Insert_Punch, Line 9
The multi-part identifier "dbo.TIMESHEETITEM.TIMESHEETITEMID" could not be bound.
Msg 4104, Level 16, State 1, Procedure Insert_Punch, Line 13
The multi-part identifier "dbo.TIMESHEETITEM.Startdtm" could not be bound.

This is the trigger now.

ALTER TRIGGER [dbo].[Insert_Punch] ON [dbo].[TIMESHEETITEM]
FOR INSERT
AS
BEGIN

SET NOCOUNT ON;

DECLARE @PrevTimeSheetId INT
SELECT TOP 1 @PrevTimeSheetId = [dbo].[TIMESHEETITEM].[TIMESHEETITEMID]
FROM TIMESHEETITEM t
JOIN inserted i ON t.EMPLOYEEID = i.EMPLOYEEID
WHERE [dbo].[TIMESHEETITEM].[STARTPUNCHEVENTID] Is Not Null
And [dbo].[TIMESHEETITEM].[ENDPUNCHEVENTID] is not null order by [dbo].[TIMESHEETITEM].[Startdtm] desc

insert A_Table(TimeSheetId,StartPunchId, Startdtm,Name,Flag, Ignore,duration,complete,employeeid,PersonNum,PreviousTimeSheetId)
select TimeSheetItemID,STARTPUNCHEVENTID,StartDTM,p.FullNM,0,0,0,0,employeeid,p.PersonNum, @PrevTimeSheetId from inserted i
left join Person p on p.PersonID = i.EmployeeID
END

My Free .NET Controls at www.qiosdevsuite.com Includes 30 Controls, Ribbon Toolbar, Ribbon Form etc...

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-27 : 08:55:56

DECLARE @PrevTimeSheetId INT
SELECT TOP 1 @PrevTimeSheetId = t.TIMESHEETITEMID]
FROM TIMESHEETITEM t
JOIN inserted i ON t.EMPLOYEEID = i.EMPLOYEEID
WHERE t.[STARTPUNCHEVENTID] Is Not Null
And t.[ENDPUNCHEVENTID] is not null order by t.[Startdtm] desc

drawlings
Starting Member

14 Posts

Posted - 2013-03-27 : 09:33:42

Works like a charm.. Thank you.

My Free .NET Controls at www.qiosdevsuite.com Includes 30 Controls, Ribbon Toolbar, Ribbon Form etc...

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-27 : 09:36:59

quote:
Originally posted by drawlings

Works like a charm.. Thank you.
My Free .NET Controls at www.qiosdevsuite.com Includes 30 Controls, Ribbon Toolbar, Ribbon Form etc...

Welcome

Note: Use table alias names while accessing respective table columns

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources