Re: Modification Dates

Hi Nigel

> I suspect you're misunderstanding something about triggers, an on
> update
> trigger setting a such a field to the current timestamp shouldn't be
> causing a
> second update. You're actually doing an update statement within the
> trigger I
> presume? That's not the way, just set NEW.modified to the value you
> want,
> eg. the current timestamp.

I'm doing it this way but I've read somewhere that this causes a second
UPDATE. If that's not the case, the better!

However, I seem to be missing something else. All my tables contain a
column...

modified TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()

The following function exists...

CREATE FUNCTION touch() RETURNS OPAQUE AS 'BEGIN new.modified = NOW();
RETURN ne
w; END;' LANGUAGE 'plpgsql';

And all tables have the following trigger defined...

CREATE TRIGGER _modified AFTER UPDATE ON any_table FOR EACH ROW EXECUTE
PROCEDURE
touch();

All this returned no errors. I do get a notice though:
NOTICE: CreateTrigger: changing return type of function touch() from
OPAQUE to TRIGGER

I had the impression that now the modified-column should be set to the
NOW() whenever an UPDATE is made on the row. That's not the case, the
value remains unchanged. What's wrong with this?

Your help is greatly apprechiated! -sven