From: Franco Bruno Borghesi <franco@akyasociados.com.ar>
Organization: AK y Asociados S.R.L.
To: "Stefan Sturm" <mailling@anrath.info>, <pgsql-sql@postgresql.org>
Subject: Re: Trigger
Date: Thu, 10 Apr 2003 13:12:49 -0300
User-Agent: KMail/1.5
References: <007401da8b57$925ff9f0$0200a8c0@sturmxp>
In-Reply-To: <007401da8b57$925ff9f0$0200a8c0@sturmxp>
MIME-Version: 1.0
Content-Type: multipart/signed; protocol="application/pgp-signature";
	micalg=pgp-sha1; boundary="Boundary-02=_CgZl+3p6pikbgXm";
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <200304101312.50325.franco@akyasociados.com.ar>

--Boundary-02=_CgZl+3p6pikbgXm
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Description: signed data
Content-Disposition: inline

I usually use plpgsql to program my triggers. The plpgsql manual is here:=
=20
http://www.postgresql.org/docs/view.php?version=3D7.3&idoc=3D1&file=3Dplpgs=
ql.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:=3Dcurrent_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=20
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=3D'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...

--Boundary-02=_CgZl+3p6pikbgXm
Content-Type: application/pgp-signature
Content-Description: signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (FreeBSD)

iD8DBQA+lZgCz/PwDAvtu7oRAs7PAKCPhnyzvcJodjwtSnUJU+y7QXSm4ACfeiPP
A+9qB32uOaiRSch6gT9c8SU=
=dqwQ
-----END PGP SIGNATURE-----

--Boundary-02=_CgZl+3p6pikbgXm--