Update Trigger - 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

General SQL Server Forums

New to SQL Server Programming

Update Trigger

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2013-11-11 : 12:00:44

I want to update a field with a trigger only if a specific field is updated.

When I try the code below, it updates the field when any field in the record is updated. Is there a way to only make look at picked_dt?


ALTER TRIGGER [dbo].[UpdatePickedDate]
on [dbo].[oeordlin_sql]
after update
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
    if UPDATE(picked_dt)
    update oeordlin_sql
    set oeordlin_sql.picked_dt = SYSDATETIME()
    from oeordlin_sql as O join Inserted as I on I.ord_no = O.ord_no and I.item_no = O.item_no and
    I.line_no = O.line_no 
    where I.picked_dt is not null 

END

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-11 : 12:12:35

If more than one row is updated and if any one of those ahd picked_dt updated, then it would update all the updated rows. If that is the problem, to prevent that, add an additional condition in the where clause - something like "and i.picked_dt <> o.picked_dt".

If that is not the issue, I don't know what is causing the behavior you are seeing.

Vack
Aged Yak Warrior

530 Posts

Posted - 2013-11-11 : 12:23:50

Here is what happens when I add I.picked_dt <> o.picked_dt

Through my previous testing my record had a timestamp in the picked_dt of 2013-11-11 12:15:12:628

After adding the above and updating a different column than picked_dt it removed the time: 2013-11-11 00:00:00 000

Vack
Aged Yak Warrior

530 Posts

Posted - 2013-11-11 : 12:37:58

After further testing, here is what I think is happening.

The program that writes to the database is updating the entire record even if the field doesn't change.

If I perform an update statement through sql to another field, the picked_dt is not updated. If I do it to the picked_dt it works like expected.

Going into the program and performing a function I know doesn't update the picked_dt but another field, it still updates the picked_dt.

So the Code above does work.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-11 : 12:39:03

I noticed only now, that you are aliasing the table and then using the original table name in the update and set statements

    if UPDATE(picked_dt)
    update O
    set O.picked_dt = SYSDATETIME()
    from oeordlin_sql as O join Inserted as I on I.ord_no = O.ord_no and I.item_no = O.item_no and
    I.line_no = O.line_no 
    where I.picked_dt is not null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources