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