Re: trigger fired on changes in specific column

On Friday 23 Aug 2002 7:52 am, Dennis Bjorklund wrote:

> I have a table where the rows are partitioned into small groups. When a
> value in column A changes, then every value in the same group would have
> their column B value updated. This would in turn trigger more things, If
> the trigger is fired only when column A changes the problem is solved.
>
> After this I need to do almost the same procedure where a value of column
> B is changed to update the column C for all rules in the group. This
> ordering of triggers would work automatically if they fire just when the
> correct column are updated.
>
> Well, I can work around these things. One way is to place B and C in their
> own tables so updating these don't trigger anything in the original table.
> I also think I can keep them in the relation by being carefull and not
> update unless the value actually changed, this costs a litle extra CPU
> time.

A single IF OLD.x=NEW.x for each column won't slow you down noticably. Indeed,
if changes cascade A=>B=>C then you might well be able to optimise for case
A.

However, I think you need to look at your data definitions - this sound
horribly unnormalized. I'm not a normalisation fascist, but this just feels
*very* wrong.

Is there a good reason why you don't decompose B,C etc to their own tables and
join where necessary. You can probably keep things looking the same (or at
least very similar) using an appropriate view.

Of course, you might have damn good reasons why none of the above helps in
this case.

HTH

- Richard Huxton