| Author |
 Topic  |
|
|
drawlings
Starting Member
United Kingdom
13 Posts |
 Posted - 03/13/2013 : 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.
Cheers
Dan |
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 03/13/2013 : 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
United Kingdom
13 Posts |
Posted - 03/13/2013 : 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
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 03/13/2013 : 16:19:27
|
-- I like the way to have a different trigger for each event
-- insert happens
CREATE TRIGGER trigger_name_for_insert ON y
FOR INSERT
AS
BEGIN
SET NOCOUNT ON;
insert x(PersonNum, InputPunchTime, OutputPunchTime, EventType)
select PersonNum, InputPunchTime, OutputPunchTime, 'P10' from inserted
END
-- update happens
CREATE TRIGGER trigger_name_for_update ON y
FOR UPDATE
AS
BEGIN
SET NOCOUNT ON;
insert x(PersonNum, InputPunchTime, OutputPunchTime, EventType)
select PersonNum, InputPunchTime, OutputPunchTime, 'P20' from inserted
END
Too old to Rock'n'Roll too young to die. |
|
|
|
drawlings
Starting Member
United Kingdom
13 Posts |
Posted - 03/13/2013 : 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
United Kingdom
13 Posts |
Posted - 03/14/2013 : 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 currently
CREATE TRIGGER Insert_Punch ON TIMESHEETITEM
FOR INSERT
AS
BEGIN
SET NOCOUNT ON;
insert TBL_ENTRY(PunchTime,EmployeeID, EntryType)
select StartDTM,EmployeeId, 'P10' from inserted
END
I 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
Dan
My Free .NET Controls at www.qiosdevsuite.com Includes 30 Controls, Ribbon Toolbar, Ribbon Form etc...
|
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 03/14/2013 : 06:02:11
|
insert TBL_ENTRY(PunchTime,EmployeeID, EntryType, FullNM)
select i.StartDTM,i.EmployeeId, 'P10', p.FullNM
from inserted i
left join Person p on p.PersonNum = i.EmployeeID
Too old to Rock'n'Roll too young to die. |
|
|
|
drawlings
Starting Member
United Kingdom
13 Posts |
Posted - 03/14/2013 : 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 UPDATE
AS
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.EmployeeID
END
My Free .NET Controls at www.qiosdevsuite.com Includes 30 Controls, Ribbon Toolbar, Ribbon Form etc...
|
Edited by - drawlings on 03/14/2013 06:57:57 |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 03/14/2013 : 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.EmployeeID
END
Too old to Rock'n'Roll too young to die. |
Edited by - webfred on 03/14/2013 07:35:25 |
|
|
| |
Topic |
|
|
|
SQL Trigger
