Date: Tue, 10 Sep 2002 18:28:16 -0500
From: "Ross J. Reedstrom" <reedstrm@rice.edu>
To: Michael Paesold <mpaesold@gmx.at>
Cc: Adam Erickson <adamre@cox.net>, pgsql-sql@postgresql.org
Subject: Re: Rules and Triggers
Message-ID: <20020910232816.GA3983@rice.edu>
Mail-Followup-To: "Ross J. Reedstrom" <reedstrm@ece.rice.edu>,
	Michael Paesold <mpaesold@gmx.at>, Adam Erickson <adamre@cox.net>,
	pgsql-sql@postgresql.org
References: <ALEKKHACBIAEBNBPFKPCGEJNEKAA.adamre@cox.net>
	<007c01c25902$9772b940$4201a8c0@beeblebrox>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <007c01c25902$9772b940$4201a8c0@beeblebrox>
User-Agent: Mutt/1.3.27i

On Tue, Sep 10, 2002 at 09:45:16PM +0200, Michael Paesold wrote:
> 
> Adam Erickson wrote:
> 
> > Correct me if I'm wrong, but rules constrain the SQL (ie. validation).
> > Triggers are carried out after the SQL is executed and the data is
> modified.
> > So, to answer your question, I think rules come first.  As to which order
> > the rules / triggers are executed probably depends on the order you put
> them
> > in when you created them but I pulled that straight out of my arse.  :)
> 
> That would be correct for triggers with AFTER INSERT/UPDATE/DELETE.
> But what about triggers BEFORE INSERT/UPDATE/DELETE?

An important thing to remember about rules vs. triggers: Rules operate
on an SQL _query_ that involves the table (or view) that the rule is
attached to, whereas triggers operate on the individual _tuples_ of the
table they are attached to. Rules act to rewrite the incoming query,
at the very earliest step: just after parsing, but before planning and
optimizing. So rules _always_ act first. Triggers act when storage gets
hit: when the optimizer has choosen a plan, and passes it to the executor
for, well, execution.

Ross