|
Posted by ZeldorBlat on 10/01/06 00:23
Jonas Smith wrote:
> I'm trying to understand triggers before trying my hand at implementing
> one. I tried reading the documentation on mysql's site, but it's not
> detailed enough.
>
> I have a large table. There is a calculation currently done on the
> client side that I would like to put on the server side.
>
> The calculation needs to happen on updates.
>
> the table has many columns but to simplify:
>
> table
> idColumn
> articleTitleColumn
> totalColumn
> votesColumn
> canceledColumn
> scoreColumn
> accessColumn
>
> Whenever a member accesses the news article, the 'accessColumn' is
> incremented by one.
>
> When a member rates the article, the totalColumn is incremented by the
> amount of the score and the votesColumn is incremented by one.
>
> Under certain conditions some votes are cancelled; in those cases the
> cancelled column is incremented.
>
> The calculation is:
>
> scoreColumn = totalColumn / (votesColumn - canceledColumn)
>
> 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?
>
> thanks in advance for any pointers.
>
> --
> Jonas
I don't think MySQL will let you define a trigger that only fires when
certain columns are updated (some other databases do). 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
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?
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.
Hope that helps.
Navigation:
[Reply to this message]
|