SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Trigger Select Lookup Value
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

drawlings
Starting Member

United Kingdom
13 Posts

Posted - 03/27/2013 :  07:55:35  Show Profile  Reply with Quote
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...

Edited by - drawlings on 03/27/2013 08:53:33

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 03/27/2013 :  08:31:53  Show Profile  Reply with Quote
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

then use this local variable to insert into table
Go to Top of Page

drawlings
Starting Member

United Kingdom
13 Posts

Posted - 03/27/2013 :  08:53:16  Show Profile  Reply with Quote
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...
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 03/27/2013 :  08:55:56  Show Profile  Reply with Quote
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
Go to Top of Page

drawlings
Starting Member

United Kingdom
13 Posts

Posted - 03/27/2013 :  09:33:42  Show Profile  Reply with Quote
Works like a charm.. Thank you.

My Free .NET Controls at www.qiosdevsuite.com Includes 30 Controls, Ribbon Toolbar, Ribbon Form etc...
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 03/27/2013 :  09:36:59  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000