Re: [SQL] auto datetime update on UPDATE?

Tulassay Zsolt <zsolt@tek.bke.hu> writes:

> Hi,
> 
> i have set up a table, where two fields (creation_dt and modification_dt)
> always get the actual date/time by default when doing an INSERT.
> my question is, how is it possible to let the field modification_dt
> change when doing an UPDATE?
> Is there a way to solve it in SQL?

To do this I use a trigger in plpgsql :

create function liens_maj_fun () returns opaque as '
begin       new.liens_maj := ''now'';       return new;
end;
' language 'plpgsql';

create trigger liens_maj_trig before update or insert   on liens for each row execute procedure liens_maj_fun();


Since it uses plpgsql don't forget to add it when you create your
base:


CREATE FUNCTION plpgsql_call_handler()  RETURNS opaque  AS '/usr/local/pgsql/lib/plpgsql.so'  LANGUAGE 'c';

CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'  HANDLER plpgsql_call_handler  LANCOMPILER 'PL/pgSQL';


-- 
christophe.labouisse@dial.oleane.com
Le cinéma en Lumière : http://www.lumiere.org/
Vote en cours pour la création du forum fr.rec.arts.sf.starwars
pour en savoir plus : <news:38146a03.373985713@news.mp-poissy.ac-versailles.fr>