0

Here are two tables : table1

id1(pk) objectid z
0        1       100
1        2       98

table2

id2(pk) n_sd prof alt_inf
0        1   5
1        1   15
2        2   8

objectid -> n_sd (1->n).

The aim of the trigger is on insert on table2 to do the maths alt_inf = z-prof for objectid = n_sd.

IF (TG_OP = 'INSERT') THEN
NEW.alti_inf = (select z-NEW.prof from table1 join table2
on table2.n_sd = table1.objectid 
where new.gid = table2.gid )
RETURN NEW;
END IF ;

DROP TRIGGER IF EXISTS trg_calcul_altiinf ON table2 ;
CREATE TRIGGER trg_calcul_altiinf AFTER INSERT ON table2
FOR EACH ROW EXECUTE PROCEDURE calcul_altiInf();

I have no result nor error message.

1

In a trigger fired AFTER the insertion it makes no sense to modify the NEW record, as it has been already inserted. Use a BEFORE trigger.

CREATE TRIGGER trg_calcul_altiinf 
BEFORE INSERT OR UPDATE ON table2    -- + UPDATE
FOR EACH ROW EXECUTE PROCEDURE calcul_altiInf();

In the function body you should query only the table1, as the current value of n_sd is in the NEW record:

IF TG_OP IN ('INSERT', 'UPDATE') THEN    -- + UPDATE
    NEW.alti_inf = (
        SELECT z - NEW.prof 
        FROM table1 
        WHERE NEW.n_sd = table1.objectid);
    RETURN NEW;
END IF;
  • more simple indeed. Thanks a lot for the explanation of BEFORE and AFTER that are not obvious to me. – Leehan Oct 20 '17 at 13:07
  • In case of UPDATE, I Wrote NEW.alti_inf = (select z - NEW.prof from test.table1 where OLD.n_sd = objectid); It does not work. And the first thing I wonder about is : should I use UPDATE table1 SET NEW.alti=... ? I tried but as it failed as well, I don't kow which part is wrong (maybe both !). Thx – Leehan Oct 23 '17 at 12:12
  • In case of UPDATE the logic is exactly the same, see the edited answer. – klin Oct 23 '17 at 12:45
  • That is what I was thinking about but update fails. I tried also : IF (TG_OP = 'INSERT') THEN NEW.alti_inf = (select z - NEW.prof from table1 where NEW.n_sd = objectid) ; RETURN NEW ; -- A la mise à jour, calcul de l'altitude inférieure ELSIF (TG_OP = 'UPDATE') THEN --UPDATE .unite_strati SET NEW.alti_inf = (select z - NEW.prof from table1 where OLD.n_sd = objectid); RETURN NEW ; END IF ; No way... – Leehan Oct 23 '17 at 13:08
  • It works pretty well, test it here. – klin Oct 23 '17 at 13:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.