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 |
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 recordThis is my trigger that inserts the new record into another table from the timesheet tableALTER TRIGGER [dbo].[Insert_Punch] ON [dbo].[TIMESHEETITEM]FOR INSERTAS 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.EmployeeIDENDIn 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 valueSELECT 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 errorMsg 4104, Level 16, State 1, Procedure Insert_Punch, Line 12The multi-part identifier "dbo.TIMESHEETITEM.STARTPUNCHEVENTID" could not be bound.Msg 4104, Level 16, State 1, Procedure Insert_Punch, Line 13The multi-part identifier "dbo.TIMESHEETITEM.ENDPUNCHEVENTID" could not be bound.Msg 4104, Level 16, State 1, Procedure Insert_Punch, Line 9The multi-part identifier "dbo.TIMESHEETITEM.TIMESHEETITEMID" could not be bound.Msg 4104, Level 16, State 1, Procedure Insert_Punch, Line 13The multi-part identifier "dbo.TIMESHEETITEM.Startdtm" could not be bound.This is the trigger now.ALTER TRIGGER [dbo].[Insert_Punch] ON [dbo].[TIMESHEETITEM]FOR INSERTAS 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.EmployeeIDENDMy 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...
WelcomeNote: Use table alias names while accessing respective table columns--Chandu |
|
|
|
|
|
|
|