Message-ID: <3E415F97.62A6E585@Yahoo.com>
Date: Wed, 05 Feb 2003 14:01:43 -0500
From: Jan Wieck <JanWieck@Yahoo.com>
Organization: Home
MIME-Version: 1.0
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>
Cc: Neal Lindsay <neal.lindsay@peaofohio.com>,
	pgsql-sql@postgresql.org
Subject: Re: automatic time/user stamp - rule or trigger?
References: <Pine.LNX.4.44.0302051745400.7803-100000@matrix.gatewaynet.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Achilleus Mantzios wrote:
> 
> On Wed, 5 Feb 2003, Neal Lindsay wrote:
> 
> > I have a table that I want to keep track of the user who last modified
> > it and the timestamp of the modification. Should I use a trigger or a rule?
> >
> > CREATE TABLE stampedtable (
> >       stampedtableid SERIAL PRIMARY KEY,
> >       updatestamp timestamp NOT NULL DEFAULT now(),
> >       updateuser name NOT NULL DEFAULT current_user,
> >       sometext text
> > );
> >
> > I suspect that I want a rule, but all the examples in the documentation
> > seem to update a separate table and not override (or add) the
> > insert/update to the timestamp and name columns.
> 
> You may want to use rules if you need rewriting.
> What you actually need is some sort of driver to a specific table.
> You could create a view to that table (to hide the accounting columns),
> and then create rules on that view that do the job as you wish.

I'm sure you want to use a BEFORE INSERT OR UPDATE trigger that modifies
NEW.updatestamp in place just before the row get's written.

A rule will not work here because rules cannot cause the same action on
the same table they are called for.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #