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