|
|
Posted by Jonas Smith on 10/03/06 20:37
On Sat, 30 Sep 2006 20:23:49 -0400, ZeldorBlat wrote
(in article <1159662229.568323.198440@c28g2000cwb.googlegroups.com>):
> Jonas Smith wrote:
>> The first question is:
>>
>> Can the trigger be set to do the calculation only when the
>> totalColumn is updated? The access column is updated quite often (as
>> well as other columns) and to trigger the calculation any time any
>> of the columns is updated would put undue load on the servers.
>>
>> Second question:
>>
>> What is the syntax for this kind of trigger?
>
> I don't think MySQL will let you define a trigger that only fires when
> certain columns are updated (some other databases do).
That's what I thought, I just needed the confirmation. And since this is
the case, a trigger for this function is not worth the effort and will
put undue load on the servers.
> You can limit
> what get's updated using the where clause of the update statement
> however. Something like this should work:
>
> create trigger myTrigger
> after update on myTable for each row
> update myTable set scoreColumn = totalColumn / (votesColumn -
> canceledColumn)
> from myTable m
> where m.idColumn = NEW.idColumn
> and NEW.totalColumn != OLD.totalColumn
Thanks.
> In your OP you said you only wanted it to update when the value of
> totalColumn changed (which is what the above does). But shouldn't it
> also update the the score when votesColumn and/or canceledColumn also
> changes?
No. If the cancelledColumn gets updated, then the votesColumn is too,
but the totalColumn is not and there is no need to recalculate.
However, if the totalColumn gets updated, that means the votesColumn is
and there is a need to recalculate.
> You will also need to create a similar trigger for inserts -- just
> replace the "after update" with "after insert" and give the trigger a
> different name.
No, no need to calculate on insert. The initial values are always zeros
for all four columns.
> Hope that helps.
Thanks. It does in a way.
--
Jonas
[Back to original message]
|