On Update (trigger hint)

Hi guys,

Please, give me some advices on how to do the
following:

I have the following table:

CREATE TABLE products (
id SERIAL,
description TEXT,
lastupdate date
);

Well, I would like to update the column "lastupdate"
with the value "now()" on every UPDATE executed on a
row of this table.

Do I have to create a function to do it? Can you help
me?

Regards,

Marcelo

______________________________________________________________________

Yahoo! Mail - O melhor e-mail do Brasil! Abra sua conta agora:
http://br.yahoo.com/info/mail.html

MaRCeLO PeReiRA wrote:

> Hi guys,
>
> Please, give me some advices on how to do the
> following:
>
> I have the following table:
>
> CREATE TABLE products (
> id SERIAL,
> description TEXT,
> lastupdate date
> );
>
> Well, I would like to update the column "lastupdate"
> with the value "now()" on every UPDATE executed on a
> row of this table.
>
> Do I have to create a function to do it? Can you help
> me?
>
> Regards,
>
> Marcelo
<snip>

/* Your function */
CREATE FUNCTION set_lastchg() RETURNS opaque AS '
BEGIN
NEW.lastupdate = now();
RETURN NEW;
END;
'LANGUAGE 'plpgsql';

/* and the triggers that will use it, All my main tables have a
lastupdate column and a trigger to execute set_lastchg()
whenever the row is changed */

CREATE TRIGGER lastclubchg_trig
BEFORE INSERT OR UPDATE ON club FOR EACH
ROW EXECUTE PROCEDURE set_lastchg();

CREATE TRIGGER lastownerchg_trig
BEFORE INSERT OR UPDATE ON owner FOR EACH
ROW EXECUTE PROCEDURE set_lastchg();

--
11:25am up 4 days, 1:32, 1 user, load average: 1.29, 1.38, 1.29
To email me, change .com to .ca Linux Counter Registration #126647