From: Tom Lane <tgl@sss.pgh.pa.us>
To: Bradley Kieser <brad@kieser.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Rule to fill in value on column on insert
Date: Mon, 10 Jun 2002 09:22:28 -0400
Message-ID: <5408.1023715348@sss.pgh.pa.us> (raw)
In-Reply-To: <3D046290.2060906@kieser.net>
References: <3D046290.2060906@kieser.net>
Bradley Kieser <brad@kieser.net> writes:
> How do I create a rule that will set a column to a particular value upon
> insert? It's for use within an audit trail and we want to prevent any
> possibility of some rogue code setting it to an incorrect value (it's a
> time stamp) so we don't want to use default values.
Use a trigger function, not a rule. A "BEFORE INSERT" trigger can do
this trivially, eg:
NEW.insertiontime := now();
return NEW;
You'll probably want a BEFORE UPDATE trigger as well, to prevent later
changes:
NEW.insertiontime := OLD.insertiontime;
return NEW;
or if you want to update the column during updates, you could actually
share the first trigger for both purposes.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, brad@kieser.net
Subject: Re: Rule to fill in value on column on insert
In-Reply-To: <5408.1023715348@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox