Re: Trigger

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...