I'm using PGSQL 7 to create a new database, and would like to track timestamps
and user names for record insertions and updates. Rather than declare the fields
for each table, I've created a 'base' table that will be inherited by all
others. Here is a simplified example:
CREATE TABLE base (
create_time TIMESTAMP DEFAULT TEXT 'now',
create_user TEXT DEFAULT CURRENT_USER,
mod_time TIMESTAMP DEFAULT TEXT 'now',
mod_user TEXT DEFAULT CURRENT_USER
);
CREATE TABLE person (
name TEXT PRIMARY KEY NOT NULL
) INHERITS(base);
On insert, the creation time and user will be set by default, and this works
fine for the person table. The problem is that I don't know how to force
mod_time and mod_user to be changed whenever a person record is updated, unless
I create a trigger explicitly for the person table. For instance, the following
trigger will only update the mod_user field of the base table:
CREATE TRIGGER base_mod_user
BEFORE UPDATE ON base
FOR EACH ROW
EXECUTE PROCEDURE insert_username(mod_user);
I had hoped that I could append an asterisk to the base table name as you can
with the SELECT clause, but it causes an error, eg:
CREATE TRIGGER base_mod_user
BEFORE UPDATE ON base*
FOR EACH ROW
EXECUTE PROCEDURE insert_username(mod_user);
Is there any way to inherit the behaviour of a trigger? Is there a simpler way
to accomplish this task without writing almost identical triggers for every
single table?
Thanks in advance for any suggestions,
Ian Harder
