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" ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
