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?

Question

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.

Answer 1

You could use a trigger to update the other field.

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

Answer 2

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

Answer 3

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.