Received: from gate-w.wettzell.ifag.de (gate-w.ifag.de [141.74.1.1]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9GCn6P27391 for ; Tue, 16 Oct 2001 08:49:06 -0400 (EDT) (envelope-from dassing@wettzell.ifag.de) Received: by gate-w.wettzell.ifag.de (8.9.3/8.9.3) id OAA17686 for pgsql-sql@postgresql.org; Tue, 16 Oct 2001 14:49:03 +0200 (CEST) Received: (from localhost) by gate-w.wettzell.ifag.de (MSCAN) id 2/gate-w.wettzell.ifag.de/smtp-gw/mscan; Tue Oct 16 14:49:03 2001 Message-ID: <3BCC2CBB.9C3A31EF@wettzell.ifag.de> Date: Tue, 16 Oct 2001 14:48:59 +0200 From: Reiner Dassing Organization: BKG-Wettzell X-Mailer: Mozilla 4.75 [en] (Windows NT 5.0; U) X-Accept-Language: de,en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Triggers do not fire Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200110/208 X-Sequence-Number: 5163 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