Re: Automatic mod time?

On 11 Mar 2002 at 15:50, Steve Lane wrote:

> It seems like this should be simple but I haven¹t found any obvious
> pointer in the documents.
>
> I need a timestamp field that¹s updated every time a record is touched,
> to show the last mod date.
>
> I have this working with a trigger just fine. But this means adding a
> new trigger for each table that needs this.
>
> Is there an easier way?

hi Steve,

i'm a complete newbie, so use this at your own risk... i ran into the
same question (+ i also want to keep track of the user touching the
record), and did it with inheritance:

---------------------------------------------------
-- this is a templatetable for the audit fields
create table au_col (
mut_id varchar(100) not null default current_user,
mut_timestamp timestamp not null default CURRENT_TIMESTAMP
);

-- function for updating the values
create function au_col()
returns opaque
as 'begin
old.mut_id = current_user;
old.mut_timestamp = CURRENT_TIMESTAMP;
return old;
end;'
language 'plpgsql';

-- trigger to call the function
create trigger au_col
before update or delete
on au_col
for each row
execute procedure au_col();

-- now, for every table you create, inherit it from au_col
create table land (
id serial primary key,
code char(2) unique not null,
name varchar(100) not null
) inherits (au_col);
---------------------------------------------------

HTH, HAND,

--
Jules