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,
