SQL Trigger On Update - SQL Server Forums

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

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

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources