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
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 SQL Trigger On Update

Author  Topic 

drawlings
Starting Member

14 Posts

Posted - 2013-03-25 : 04:19:40
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-25 : 04:45:39
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
Go to Top of Page

drawlings
Starting Member

14 Posts

Posted - 2013-03-25 : 05:24:02
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

52326 Posts

Posted - 2013-03-25 : 11:28:32
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
   

- Advertisement -