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
 General SQL Server Forums
 New to SQL Server Programming
 SQL Trigger

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.

Cheers

Dan

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.
Go to Top of Page

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.
Go to Top of Page

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 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
[/code]


Too old to Rock'n'Roll too young to die.
Go to Top of Page

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...
Go to Top of Page

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 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...
Go to Top of Page

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 i
left join Person p on p.PersonNum = i.EmployeeID




Too old to Rock'n'Roll too young to die.
Go to Top of Page

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 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...
Go to Top of Page

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.EmployeeID
END



Too old to Rock'n'Roll too young to die.
Go to Top of Page
   

- Advertisement -