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
 General SQL Server Forums
 New to SQL Server Programming
 SQL Trigger
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

drawlings
Starting Member

United Kingdom
13 Posts

Posted - 03/13/2013 :  15:54:13  Show Profile  Reply with Quote
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
8683 Posts

Posted - 03/13/2013 :  16:00:43  Show Profile  Visit webfred's Homepage  Reply with Quote
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

United Kingdom
13 Posts

Posted - 03/13/2013 :  16:04:38  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8683 Posts

Posted - 03/13/2013 :  16:19:27  Show Profile  Visit webfred's Homepage  Reply with Quote

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

drawlings
Starting Member

United Kingdom
13 Posts

Posted - 03/13/2013 :  16:30:42  Show Profile  Reply with Quote
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

United Kingdom
13 Posts

Posted - 03/14/2013 :  05:32:39  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8683 Posts

Posted - 03/14/2013 :  06:02:11  Show Profile  Visit webfred's Homepage  Reply with Quote
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

United Kingdom
13 Posts

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

webfred
Flowing Fount of Yak Knowledge

Germany
8683 Posts

Posted - 03/14/2013 :  07:34:37  Show Profile  Visit webfred's Homepage  Reply with Quote
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
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.11 seconds. Powered By: Snitz Forums 2000