Can you create a trigger on a field within a table?

Question

Is it possible to create a trigger on a field within a table being updated?

So if I have:

TableA
  Field1
  Field2
  ....

I want to update a certain value when Field1 is changed. In this instance, I want to update Field2 when Field1 is updated, but don't want to have that change cause another trigger invocation, etc...

Answer 1

Please provide more details and ask yourself if a trigger is really needed? In my experience, triggers like this seem to become technical debt within a matter of one or two interations.

Answer 2

You can use an INSTEAD OF trigger in this case. These are NOT fired recursively. Read here for more information on recursive triggers (search for "recursively").

As always, be VERY CAREFUL what you do with an INSTEAD OF trigger. You can do some very nasty things (like when somebody calls an insert statement, you can deflect that to be a delete statement on some random record - i.e. a very nasty thing to do!).

Answer 3

This seems to solve my problem:

CREATE TRIGGER trg_name ON TableA
AFTER UPDATE
AS 
BEGIN
IF UPDATE(FieldA) 
  update import_status set FieldB = 0
END
GO

Update:

As pointed out in the comments, this is generally a bad idea, since it would updated EVERY SINGLE ROW, which is probably not what you want. In this particular case, however, there will only ever be one row, which is just used to store the timestamp and flag field.

Answer 4

You can check if Field1 value has been changed by selecting its previous value from DELETED.

SELECT @prevValue = Field1 FROM DELETED WHERE ...