agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Franco Bruno Borghesi <franco@akyasociados.com.ar>
To: Stefan Sturm <mailling@anrath.info>
To: pgsql-sql@postgresql.org
Subject: Re: Trigger
Date: Thu, 10 Apr 2003 13:12:49 -0300
Message-ID: <200304101312.50325.franco@akyasociados.com.ar> (raw)
In-Reply-To: <007401da8b57$925ff9f0$0200a8c0@sturmxp>
References: <007401da8b57$925ff9f0$0200a8c0@sturmxp>
I usually use plpgsql to program my triggers. The plpgsql manual is here:
http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=plpgsql.html
Section 19.9 specifically talks about triggers.
Anyway, here is an example of what you said you needed:
--my table
CREATE TABLE mytest (id SERIAL, name TEXT, lastchange TIMESTAMP);
--function to set the value of the field 'lastChange' to current system tyme
CREATE OR REPLACE FUNCTION mytest_set_lastChange() RETURNS TRIGGER AS '
BEGIN
NEW.lastChange:=current_timestamp;
RETURN NEW;
END; ' LANGUAGE 'plpgsql';
--trigger that calls mytest_set_lastChange after inserts or updates
CREATE TRIGGER mytest_tg1 BEFORE INSERT OR UPDATE ON mytest FOR EACH ROW
EXECUTE PROCEDURE mytest_set_lastChange();
INSERT INTO mytest (name) VALUES ('peter');
SELECT * FROM mytest;
id | name | lastchange
----+-------+----------------------------
1 | peter | 2003-04-10 13:10:16.993779
(1 row)
UPDATE mytest SET name='joe';
id | name | lastchange
----+------+----------------------------
1 | joe | 2003-04-10 13:11:10.787253
(1 row)
hope it helps.
On Wednesday 10 April 2024 11:58, you wrote:
> t want to update to fields on instert and update with the system
> time...
view thread (14+ messages) latest in thread
Message-ID: <200304101312.50325.franco@akyasociados.com.ar>
Permalink: ../200304101312.50325.franco@akyasociados.com.ar/
Also on: postgresql.org/message-id/200304101312.50325.franco@akyasociados.com.ar
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: franco@akyasociados.com.ar, mailling@anrath.info
Subject: Re: Trigger
In-Reply-To: <200304101312.50325.franco@akyasociados.com.ar>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox