Thread: Selective Auto-Timestamp [using triggers?]

Dear List,

I've recently found that it would be useful if I could update a timestamp field in some of my tables automatically so
thatwhenever someone updates the tables in question, the timestamp reflects when that change was made. 

The thing is, I also want to be able to update the timestamp field if I want to... thus if I update, say, the 'name'
field- the timestamp field would be updated to 'now()'.  However, if I updated the timestamp field then that field
wouldsimply take on the value I had suggested. 

I managed to find a partially working solution as follows:

CREATE FUNCTION update_modtime() RETURNS opaque AS 'BEGIN IF OLD.modtime = NEW.modtime THEN NEW.modtime = now() END IF;
RETURNNEW; END;' LANGUAGE 'plpgsql'; 
CREATE TRIGGER autostamp BEFORE UPDATE ON <table-name> FOR EACH ROW EXECUTE PROCEDURE update_modtime();

The problem with this is, if I do UPDATE <table-name> set modtime = <existing-value> WHERE <condition> where
<existing-value>is the current value of modtime, modtime then takes the value of now().  But if I'm explicitly setting
itto its existing value, I don't want the trigger to do this.  Of course, I could modify my scripts and stuff so that,
ifI don't want modtime to change, I don't try to change it.  But I'm not the only user - and I think the behaviour
wouldbe more pleasant for the different users if modtime always updated to what was specified, if a user was explicitly
settingit. 

Thanks for your time,
Any help greatly appreciated.
Rajit

Rajit Singh writes:
> The thing is, I also want to be able to update the timestamp field
> if I want to...

Here's my solution:

CREATE FUNCTION updated_stamp () RETURNS OPAQUE AS
'    BEGIN
    IF NEW.updated ISNULL THEN
        NEW.updated := ''now'';
    END IF;
        RETURN NEW;
    END;
' LANGUAGE 'plpgsql';

Change:

if old.modtime = new.modtime

To:

if new.modtime is null


I *think* that's what you are looking for.

--rob

----- Original Message -----
From: "Rajit Singh" <singh.raj@studychoice.com>
To: <pgsql-general@postgresql.org>
Sent: Thursday, January 11, 2001 7:00 AM
Subject: Selective Auto-Timestamp [using triggers?]


> Dear List,
>
> I've recently found that it would be useful if I could update a timestamp
field in some of my tables automatically so that whenever someone updates
the tables in question, the timestamp reflects when that change was made.
>
> The thing is, I also want to be able to update the timestamp field if I
want to... thus if I update, say, the 'name' field - the timestamp field
would be updated to 'now()'.  However, if I updated the timestamp field then
that field would simply take on the value I had suggested.
>
> I managed to find a partially working solution as follows:
>
> CREATE FUNCTION update_modtime() RETURNS opaque AS 'BEGIN IF OLD.modtime =
NEW.modtime THEN NEW.modtime = now() END IF; RETURN NEW; END;' LANGUAGE
'plpgsql';
> CREATE TRIGGER autostamp BEFORE UPDATE ON <table-name> FOR EACH ROW
EXECUTE PROCEDURE update_modtime();
>
> The problem with this is, if I do UPDATE <table-name> set modtime =
<existing-value> WHERE <condition> where <existing-value> is the current
value of modtime, modtime then takes the value of now().  But if I'm
explicitly setting it to its existing value, I don't want the trigger to do
this.  Of course, I could modify my scripts and stuff so that, if I don't
want modtime to change, I don't try to change it.  But I'm not the only
user - and I think the behaviour would be more pleasant for the different
users if modtime always updated to what was specified, if a user was
explicitly setting it.
>
> Thanks for your time,
> Any help greatly appreciated.
> Rajit
>