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-13 : 15:54:13
|
Hi All newbie to the forum,I'm not very familiar with SQL Triggers, but I need one to create a new record in table x after a record has been updated or created in table y.Table y consists of a number of fields, but I am only interested in a few ('PersonNum', 'InputPunchTime','OutputPunchTime'). What happens is a record is created in table y that fills in the first 2 fields 'PersonNim' & 'InputPunchTim'. Then later on, that record is then updates the 'OutputPunchTime'. So I need to create a new record for each of these events, there would be 2 new records entered into table x. In Table x there is also an additional field called 'EventType', so if a new record is created with the InputTime it would have a value of P10 and if its Output it would have P20.Sorry if this sounds confusing I didn't write the software. I just created table x as I need to extract the data daily into a format that can be imported into SAP. CheersDan |
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-03-13 : 16:00:43
|
so your table will have 2 records in x for 1 entry in y... but what do you want to happen if more than 1 update happens on a record in y? Too old to Rock'n'Roll too young to die. |
|
|
drawlings
Starting Member
14 Posts |
Posted - 2013-03-13 : 16:04:38
|
Yes, 1 record in x and 2 records in x. The database is a time & attendance db, so for each record it will be created and only updated once. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-03-13 : 16:19:27
|
[code]-- I like the way to have a different trigger for each event-- insert happensCREATE TRIGGER trigger_name_for_insert ON yFOR INSERTAS BEGIN SET NOCOUNT ON; insert x(PersonNum, InputPunchTime, OutputPunchTime, EventType) select PersonNum, InputPunchTime, OutputPunchTime, 'P10' from insertedEND-- update happensCREATE TRIGGER trigger_name_for_update ON yFOR UPDATEAS BEGIN SET NOCOUNT ON; insert x(PersonNum, InputPunchTime, OutputPunchTime, EventType) select PersonNum, InputPunchTime, OutputPunchTime, 'P20' from insertedEND[/code] Too old to Rock'n'Roll too young to die. |
|
|
drawlings
Starting Member
14 Posts |
Posted - 2013-03-13 : 16:30:42
|
Thanks, I will give this a go in the morning.My Free .NET Controls at www.qiosdevsuite.com Includes 30 Controls, Ribbon Toolbar, Ribbon Form etc... |
|
|
drawlings
Starting Member
14 Posts |
Posted - 2013-03-14 : 05:32:39
|
It seems I have it working now. Can the trigger lookup data from another table and return a value when creating a record?This is what I have currentlyCREATE TRIGGER Insert_Punch ON TIMESHEETITEMFOR INSERTAS BEGIN SET NOCOUNT ON; insert TBL_ENTRY(PunchTime,EmployeeID, EntryType) select StartDTM,EmployeeId, 'P10' from insertedENDI need to look up into a table called Person linking the employeeId to PersonNum in table Person and return Person.FullNM. Is this possible?Thanks Again DanMy Free .NET Controls at www.qiosdevsuite.com Includes 30 Controls, Ribbon Toolbar, Ribbon Form etc... |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-03-14 : 06:02:11
|
insert TBL_ENTRY(PunchTime,EmployeeID, EntryType, FullNM)select i.StartDTM,i.EmployeeId, 'P10', p.FullNM from inserted ileft join Person p on p.PersonNum = i.EmployeeID Too old to Rock'n'Roll too young to die. |
|
|
drawlings
Starting Member
14 Posts |
Posted - 2013-03-14 : 06:56:25
|
Thanks again, I ran into another issue with the update command. All works perfectly apart from the update. This is because there is another trigger that performs and update, so therefore my trigger runs twice and doubles up on the entry.I am trying to avoid editing the update trigger from the software developers as I really don't want to mess things up. So I have added an extra couple of fields to my entry table, EntryPunchId which is generated with every event by the software. Is it possible for the trigger to first check if a record exists within the entry table before adding a record? EntryPunchId should be unique.ALTER TRIGGER [dbo].[Update_Punch] ON [dbo].[TIMESHEETITEM]FOR UPDATEAS BEGIN SET NOCOUNT ON; insert TBL_ENTRY(PunchTime,EmployeeID, EntryType,Name,TimeSheetId, PunchEventId) select EndDTM,EmployeeId, 'P20',p.FullNM,TIMESHEETITEMID,ENDPUNCHEVENTID 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... |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-03-14 : 07:34:37
|
Not sure if I am using the correct columns to check if that record already exists... but here we go:IF not exists(select * from TBL_ENTRY t where t.PunchEventId = inserted.ENDPUNCHEVENTID and t.EntryType='P20')Begin insert TBL_ENTRY(PunchTime,EmployeeID, EntryType,Name,TimeSheetId, PunchEventId) select EndDTM,EmployeeId, 'P20',p.FullNM,TIMESHEETITEMID,ENDPUNCHEVENTID from inserted i left join Person p on p.PersonID = i.EmployeeIDEND Too old to Rock'n'Roll too young to die. |
|
|
|
|
|
|
|