How can I update a small field in a big SQL table if another field in the same row is changed by an external process?

I'd like to call Update ... Set ... Where ... to update a field as soon as that evil ERP process is changing the value of another.

I'm running MS SQL.

From stackoverflow

• You could use a trigger to update the other field.

  Edit: I guess that may depend on what SQLesque database you are running.

• I can't test, but i guess its a trigger like this

  CREATE TRIGGER TriggerName ON TableName FOR UPDATE AS
    IF UPDATE(ColumnUpdatedByERP)
    BEGIN
      UPDATE ...
    END
  

  -- Edit - a better version, thanks for comment Tomalak

   
  CREATE TRIGGER TriggerName ON TableName FOR UPDATE AS
    DECLARE @oldValue VARCHAR(100)
    DECLARE @newValue VARCHAR(100)
    IF UPDATE(ColumnUpdatedByERP)
    BEGIN
      SELECT @oldValue = (SELECT ColumnUpdatedByERP FROM Deleted) 
      SELECT @newValue = (SELECT ColumnUpdatedByERP FROM Inserted) 
      IF @oldValue <> @newValue
      BEGIN
        UPDATE ...
      END
    END
  

  Tomalak : IF UPDATE() checks if the field was mentioned in the UPDATE statement only. It will return true even if the field value itself has not been changed. This is not what the OP had in mind, you might want to change your code so it compares against the "inserted" table.

  Tomalak : Thats better. +1 You could join inserted and deleted, to remove the need for extra variables: IF (SELECT CASE WHEN d.TheColumn = i.TheColumn THEN 0 ELSE 1 END FROM deleted d, inserted i) = 1 ...

  Josef : This also ignores the problem of multiple rows updated by the trigger!

• You want to use a trigger but I would be very wary of the bug in the selected answer. See Brent Ozar's well written post http://www.brentozar.com/archive/2009/01/triggers-need-to-handle-multiple-records/ on Multiple Records.