Here is tbl1 :
gid pm 1 2.1 2 2.0 3 1.95 ...
Here is tbl2 I want :
gid num pb gidTbl1 1 1 1 2 2 1 3 3 2.1 1 4 1 2 5 2 2 6 3 2 7 4 2.0 2 8 1 3 ...
On an insert in tbl2, I would like that pb = pm for gidTbl1 = gid AND for the max num in tbl2.
Here are a trigger and the function on tbl2 I wrote :
CREATE OR REPLACE FUNCTION maj_pb() RETURNS trigger LANGUAGE 'plpgsql' COST 100 VOLATILE NOT LEAKPROOF AS $BODY$ begin IF TG_OP IN ('INSERT') THEN NEW.pb = pm from tbl1 where NEW.gidTbl1 = tbl1.gid and num = (select max(num) from tbl2 where NEW.gidTbl1 = g.gid ) ; RETURN NEW ; END IF ; END ; $BODY$; CREATE TRIGGER trg_maj_pb BEFORE INSERT ON tbl2 FOR EACH ROW EXECUTE PROCEDURE activite.maj_pb();
- error message is :
num doesn't exist
I presume that the trigger is looking for
num in tbl1 (?). But I can't find an other way to write the function.
- I add : In fact, whatever I do (insert or update) the max
numby gidTbl1 (tbl2) always has to have the
pmcorresponding in tbl1.