Received: from ns.krot.org (ns.krot.org [193.212.103.165]) by postgresql.org (8.11.3/8.11.4) with SMTP id f9GJjMP63551 for ; Tue, 16 Oct 2001 15:45:22 -0400 (EDT) (envelope-from postgresql@envisity.com) Received: (qmail 14383 invoked by uid 67); 16 Oct 2001 19:43:27 -0000 Message-ID: <20011016194327.8782.qmail@ns.krot.org> Content-Type: text/plain; charset=ISO-8859-1 Content-Disposition: inline MIME-Version: 1.0 From: "Aasmund Midttun Godal" To: dassing@wettzell.ifag.de Cc: pgsql-sql@postgresql.org Subject: Re: Triggers do not fire Date: Tue, 16 Oct 2001 19:43:27 GMT X-Mailer: Googley-Moogley In-Reply-To: <3BCC2CBB.9C3A31EF@wettzell.ifag.de> from Reiner Dassing on Tue, 16 Oct 2001 14:48:59 +0200 References: <3BCC2CBB.9C3A31EF@wettzell.ifag.de> Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by postgresql.org id f9H00DO13296 X-Archive-Number: 200110/223 X-Sequence-Number: 5178 Your update trigger is fired FOR EACH ROW and no rows are updated i.e. no trigger fired! On Tue, 16 Oct 2001 14:48:59 +0200, Reiner Dassing wrote: > Hallo! > > I have written a very small test procedure to show a possible error > on PostgreSQL V7.1.1. > A trigger does not fire the function on update but on inserts. > Does someone have made the same experiences? > > > Here is the short example which is doing nothing important, just showing > the > situation: > > DROP TABLE test; > CREATE TABLE test ( > "sensor_id" int4 NOT NULL, > "epoch" datetime NOT NULL, > "value" float4 NOT NULL, > PRIMARY KEY (sensor_id,epoch)); > > DROP FUNCTION f_test(); > CREATE FUNCTION f_test() RETURNS OPAQUE AS ' > BEGIN > RAISE NOTICE ''Fired %'',TG_OP; > RETURN NULL; > END; > ' LANGUAGE 'plpgsql'; > > > DROP TRIGGER t_test; > CREATE TRIGGER t_test BEFORE INSERT OR UPDATE ON test FOR EACH ROW > EXECUTE PROCEDURE f_test(); > > INSERT INTO test VALUES(1,'2000-10-11 12:00:00',-20.2); > UPDATE test SET value = 1000.0 WHERE epoch = '2000-10-11 12:10:00' AND > sensor_id = 1; > > The result is as follows: > INSERT INTO test VALUES(1,'2000-10-11 12:00:00',-20.2); > NOTICE: Fired INSERT > INSERT 0 0 > UPDATE test SET value = 1000.0 WHERE epoch = '2000-10-11 12:10:00' AND > sensor_id = 1; > UPDATE 0 > > The insert notice can be shown! > The update notice is not there! > > Why? > > > -- > Mit freundlichen Gruessen / With best regards > Reiner Dassing > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) Aasmund Midttun Godal aasmund@godal.com - http://www.godal.com/ +47 40 45 20 46