Alvaro Herrera wrote:
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.
Shure. In that case, i will do the initial inserts into a temporary
table, do the counting, updating the helper table, and then insert into
the planets table.
I use that approach and works fine to me.
Gerardo
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
