Re: auto update dates

On 23/05/02, Joshua b. Jore (josh@greentechnologist.org) wrote:
> Check out some code I wrote at
> http://www.greentechnologist.org/political/Voter/dev/5_trig.sql. There
> are functions to keep a Created attribute invariant and update
> Modified as well.

Hi Joshua
Thanks for the mail.

I'm looking for something like your function and trigger on your page
here:
    -- DROP FUNCTION UsersUpd();
    CREATE FUNCTION UsersUpd() RETURNS OPAQUE AS '
    BEGIN
        UPDATE AuthNames SET Name = NEW.Name WHERE Name = OLD.Name;

        NEW.Created = OLD.Created;
        NEW.Modified = current_timestamp;
        NEW.AuditSeq = nextval(''AuditSeq'');

        RETURN NEW;
    END;
    ' LANGUAGE 'plpgsql' WITH (isstrict);

    -- SELECT DropTrigger('Users','UsersUpd');
    CREATE TRIGGER UsersUpd BEFORE UPDATE ON Users
        FOR EACH ROW EXECUTE PROCEDURE UsersUpd();

However my version of getting current_timestamp creates an error:

update ideas set title = 'three' where title ~ 'one';
    NOTICE:  Error occurred while executing PL/pgSQL function
    update_date_time
    NOTICE:  line 3 at assignment
    ERROR:  Bad time external representation '16:11:45.820720+01'

Any ideas on why this may be?
Thanks for your help
Rory

--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>