Received: from localhost (unknown [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 4B93E2E34DC
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 29 Nov 2007 15:06:29 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 99521-05 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 29 Nov 2007 15:06:21 -0400 (AST)
Received: from mail.fmed.uba.ar (mail.fmed.uba.ar [157.92.152.1])
	by postgresql.org (Postfix) with ESMTP id 018142E33AF
	for <pgsql-sql@postgresql.org>; Thu, 29 Nov 2007 15:06:22 -0400 (AST)
Received: from [157.92.155.58] (mailbis [157.92.155.58])
	by mail.fmed.uba.ar (Postfix) with ESMTP id 050F711B35C
	for <pgsql-sql@postgresql.org>; Thu, 29 Nov 2007 16:08:33 -0300 (ART)
Message-ID: <474F106A.5080107@fmed.uba.ar>
Date: Thu, 29 Nov 2007 16:18:02 -0300
From: Gerardo Herzig <gherzig@fmed.uba.ar>
User-Agent: Mozilla Thunderbird 1.0.7 (X11/20050923)
X-Accept-Language: en-us, en
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>
In-Reply-To: <474EC9CE.9090609@gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200711/158
X-Sequence-Number: 29686

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?
>
> -- Stephen

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.

Cheers.
Gerardo