Message-ID: <474F1C46.2080606@fmed.uba.ar>
Date: Thu, 29 Nov 2007 17:08:38 -0300
From: Gerardo Herzig <gherzig@fmed.uba.ar>
User-Agent: Mozilla Thunderbird 1.0.7 (X11/20050923)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: statement-level trigger sample out there?
References: <20071129130159.110470@gmx.net>
	<162867790711290548t2a050d59ta77595c2b05d59ce@mail.gmail.com>
	<474EC9CE.9090609@gmail.com> <474F106A.5080107@fmed.uba.ar>
	<20071129191723.GI9567@alvh.no-ip.org>
In-Reply-To: <20071129191723.GI9567@alvh.no-ip.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

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