Trigger SQL to UPDATE Only row being Updated

• gokay

  Newbie

  Points: 5

  More actions

  April 28, 2006 at 4:20 am

  #168753

  hi,

   

  i need help about sql update trigger.

  i want to update date field when status changed. but the trigger that i write update all date coloumn.

  thanks for help.

  CREATE TRIGGER trgu_TABLE_A ON lv_order AFTER UPDATE, INSERT

  AS if update(ord_statusID)

  BEGIN

   UPDATE lv_order SET ord_ExpShipDate=getdate()

    FROM inserted i INNER JOIN lv_order c ON i.ord_ID = c.ord_ID

  END

• John Rowan

  SSC Guru

  Points: 56440

  More actions

  April 28, 2006 at 3:48 pm

  #635015

  If you were to issue an UPDATE lv_order SET ord_statusID = 1 WHERE ord_id = (some valid ID), your trigger will correctly update the ord_ExpShipDate column for only the one Ord_ID row that you specify.  You have written it correctly.  The only time it will update multiple rows is if you are updating the statusID on multiple rows or inserting multiple rows at once.  If you are still having problems, can you give more detail as to what exactly is happening.  It would be helpful if you could post some sample data and the update or insert statement that you think is causing all rows to be updated. 

  John Rowan

  ======================================================
  ======================================================
  Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden