Gerardo Herzig escribió:
> Stephen Cook wrote:
>
>> I am curious (coming from a MS SQL Server background, I just started
>> playing with PostgreSQL recently).
>>
>> What type of situation would warrant a statement-level trigger that can't
>> access the old and new values? Without that access, isn't the only
>> information you get is the fact that an operation occurred on the table?
>> Or am I missing something?
>
> What about this. Suppose you have this table "planets":
> planet_name | star_id|....
>
> There is a lot of stars, right? And if a very common query involves a
> "select planet_name, count(*) from planets group by star_id"....Well, if
> there is 1.000.000.000 of galaxies, and 1.000.000.000.000 of stars per
> galaxy...Thats a lot of planets to count!!! So maybe you want a helper
> table who maintains such of subtotals.
>
> Well, each time you discover a new galaxy, insert every planet in the
> monster table, and *after* all the inserts, run a trigger for updating the
> helper table.
Right, but it would be much more useful if you can access the NEW set
and instead of counting all the planets from scratch, you just take the
current count and add the number of planets being added.
You can do it with FOR EACH ROW triggers, but it's much worse because
you need one UPDATE on the counter for each new planet.
Perhaps the usefulness is that you store _in memory_ the number of
planets added during the FOR EACH ROW trigger, and when that's done,
call the FOR EACH STATEMENT trigger that does a single update adding the
number in memory. This would work only if the FOR EACH STATEMENT
trigger was promised to be executed after all the FOR EACH ROW triggers
were called.
--
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
"No reniegues de lo que alguna vez creíste"
