Hello, I got some problem on trigger which call them self for UPDATE BEFORE/AFTER.
Here is some test : The UPDATE test function/table : -------------------------------- ------8<------------8<----------------8<---------- CREATE SEQUENCE id_my_table_seq; CREATE table "my_table" ( "id_my_table" int4 DEFAULT nextval('id_my_table_seq') PRIMARY KEY, "row0" text, "row1" text, "row2" text ); INSERT INTO my_table (id_my_table, row0, row1, row2) VALUES (10, 'data0', 'data1', 'data2'); CREATE OR REPLACE FUNCTION my_table_before_update() RETURNS trigger AS ' DECLARE BEGIN IF OLD.row0 <> NEW.row0 THEN RAISE NOTICE ''test1 %'', OLD.row0; RAISE NOTICE ''test2 %'', NEW.row0; UPDATE my_table SET row1 = \'toto\' WHERE id_my_table = NEW.id_my_table; RAISE NOTICE ''test3 %'', OLD.row0; RAISE NOTICE ''test4 %'', NEW.row0; UPDATE my_table SET row1 = \'tata\' WHERE id_my_table = NEW.id_my_table; RAISE NOTICE ''test5 %'', OLD.row0; RAISE NOTICE ''test6 %'', NEW.row0; END IF; RETURN NEW; END; ' LANGUAGE plpgsql; CREATE TRIGGER my_table_before_update BEFORE UPDATE ON my_table FOR EACH ROW EXECUTE PROCEDURE my_table_before_update(); CREATE OR REPLACE FUNCTION my_table_after_update() RETURNS trigger AS ' DECLARE BEGIN RAISE NOTICE ''test7 %'', OLD.row0; RAISE NOTICE ''test8 %'', NEW.row0; RETURN NEW; END; ' LANGUAGE plpgsql; CREATE TRIGGER my_table_after_update AFTER UPDATE ON my_table FOR EACH ROW EXECUTE PROCEDURE my_table_after_update(); ------8<------------8<----------------8<---------- The test for these trigger : ---------------------------- UPDATE my_table set row0 = 'my_test' WHERE id_my_table = 10; Result : -------- On a 7.4.7 : UPDATE my_table set row0 = 'my_test' WHERE id_my_table = 10; NOTICE: test1 data0 NOTICE: test2 my_test NOTICE: test3 data0 NOTICE: test4 my_test NOTICE: test5 data0 NOTICE: test6 my_test NOTICE: test7 data0 NOTICE: test8 data0 NOTICE: test7 data0 NOTICE: test8 data0 on a 8.1.4 (without context) : test=# update my_table set row0 = 'my_test' WHERE id_my_table = 10; NOTICE: test1 data0 NOTICE: test2 my_test NOTICE: test7 data0 NOTICE: test8 data0 NOTICE: test3 data0 NOTICE: test4 my_test NOTICE: test7 data0 NOTICE: test8 data0 NOTICE: test5 data0 NOTICE: test6 my_test PG7 dont make recursiv, it wait for the end of the trigger BEFORE_UPDATE to call the new UPDATE stat and forgot the 3rd AFTER_UPDATE. PG8 is better, it call trigger like real recursiv fonction, but allways dismiss the 3rd AFTER UPDATE. Logically, the answer should be : NOTICE: test1 data0 NOTICE: test2 my_test NOTICE: test7 data0 NOTICE: test8 data0 NOTICE: test3 data0 NOTICE: test4 my_test NOTICE: test7 data0 NOTICE: test8 data0 NOTICE: test5 data0 NOTICE: test6 my_test NOTICE: test7 data0 NOTICE: test8 my_test At beginning, i made a test to see how pl/pgsql make real recursiv with an insert function which work : -----------8<-----------8<-----------8<-----------8<--------------- CREATE SEQUENCE id_test_seq; CREATE table "test" ( "id_test" int4 DEFAULT nextval(id_test_seq) PRIMARY KEY, "test" text, "other_row" text, ); CREATE OR REPLACE FUNCTION test_insert() RETURNS trigger AS ' DECLARE categorie_mere RECORD; categorie_mere_lien RECORD; RecTmp RECORD; BEGIN RAISE NOTICE ''begginning''; IF NEW.test = ''test'' THEN INSERT INTO test (test) VALUES (''toto''); END IF; RAISE NOTICE ''end''; RETURN NEW; END; ' LANGUAGE plpgsql; CREATE TRIGGER test_insert BEFORE INSERT ON test FOR EACH ROW EXECUTE PROCEDURE test_insert(); -----------8<-----------8<-----------8<-----------8<--------------- With a : INSERT INTO test (test) values ('test'); You obtain in each case : NOTICE: begginning NOTICE: begginning NOTICE: end NOTICE: end ------------------------------------------------------------- In fact, what i dont understand, its why PG dont forget to make the 2 update inside the main update, but after, forgot to make the last one. Any idea ? Regards, ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org