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
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 SQL Trigger On Update
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

drawlings
Starting Member

United Kingdom
14 Posts

Posted - 03/25/2013 :  04:19:40  Show Profile  Reply with Quote
Hi All, I have the following trigger below that runs when a record is updated.

Is it possible to when the trigger is executed to check which field has been updated? I am interested in the ENDDTM and STARTDTM fields only. The below code only looks at the ENDDTM on the update trigger, but I have since noticed the STARTDTM field can be updated aswell


ALTER TRIGGER [dbo].[Update_Punch] ON [dbo].[TIMESHEETITEM]
FOR UPDATE
AS 
BEGIN

    SET NOCOUNT ON;

    insert A_TABLE(PunchTime,EmployeeID, EntryType,Name,TimeSheetId, PunchEventId, PersonNum)
    select  EndDTM,EmployeeId, 'P20',p.FullNM,TIMESHEETITEMID,ENDPUNCHEVENTID, p.PersonNum from inserted i
		left join Person p on p.PersonID = i.EmployeeID
		WHERE ENDPUNCHEVENTID NOT IN (SELECT PunchEventId FROM dbo.a_table)


Thanks

Dan

My Free .NET Controls at www.qiosdevsuite.com Includes 30 Controls, Ribbon Toolbar, Ribbon Form etc...

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 03/25/2013 :  04:45:39  Show Profile  Reply with Quote
Use UPDATE(columnName) function in the trigger body...
write code for checking update of particular column
Eg: IF( UPDATE(STARTDTM) )
    BEGIN
        --code what you want
    END


EDIT: I have corrected syntax... Try now

Edited by - bandi on 03/26/2013 00:27:19
Go to Top of Page

drawlings
Starting Member

United Kingdom
14 Posts

Posted - 03/25/2013 :  05:24:02  Show Profile  Reply with Quote
Thanks Bandi

I get the follow error

Msg 195, Level 15, State 10, Procedure Update_Punch, Line 5
'updated' is not a recognized built-in function name.
Msg 195, Level 15, State 10, Procedure Update_Punch, Line 16
'UPDATED' is not a recognized built-in function name.


Here is my code

ALTER TRIGGER [dbo].[Update_Punch] ON [dbo].[TIMESHEETITEM]
FOR UPDATE
AS 
	if(updated(Enddtm))

BEGIN

    SET NOCOUNT ON;

    insert A_TABLE(PunchTime,EmployeeID, EntryType,Name,TimeSheetId, PunchEventId, PersonNum)
    select  EndDTM,EmployeeId, 'P20',p.FullNM,TIMESHEETITEMID,ENDPUNCHEVENTID, p.PersonNum from inserted i
		left join Person p on p.PersonID = i.EmployeeID
		WHERE ENDPUNCHEVENTID NOT IN (SELECT PunchEventId FROM dbo.a_table)
END
IF( UPDATED(STARTDTM) )
begin
	  SET NOCOUNT ON;

    insert A_TABLE(PunchTime,EmployeeID, EntryType,Name,TimeSheetId, PunchEventId,PersonNum)
    select  StartDTM,EmployeeId, 'P10',p.FullNM,TIMESHEETITEMID,STARTPUNCHEVENTID,p.PersonNum from inserted i
		left join Person p on p.PersonID = i.EmployeeID
		WHERE ENDPUNCHEVENTID NOT IN (SELECT PunchEventId FROM dbo.a_table)
end


My Free .NET Controls at www.qiosdevsuite.com Includes 30 Controls, Ribbon Toolbar, Ribbon Form etc...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/25/2013 :  11:28:32  Show Profile  Reply with Quote
it should be UPDATE and not UPDATED

see

http://msdn.microsoft.com/en-IN/library/8e3be25b-2e3b-4d1f-a610-dcbbd8d72084



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

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.06 seconds. Powered By: Snitz Forums 2000