0

Here's a sample of log :

gid alti  numlog
1   79.55 1
2   79.65 2

Here's a sample of us :

gid numlog numus sommet base alti_sommet alti_base
1   1      1000  0      0.25 (79.55-0)   (79.55-0.25)
2   1      1001  0.25   0.39 (79.55-0.25)(79.55-0.39)
3   1      1002  0.39   0.59 (79.55-0.39) ...
4   2      1000  0      0.15 (79.65-0)
5   2      1001  0.15   0.40 (79.65-0.15)

With a trigger on us, I would like to update alti_sommet and alti_base with the result of the math between ().

I create this function just for alti_base for now :

create or replace function activite.calcul_alti_us()
returns trigger as
$body$
 BEGIN
  IF (TG_OP IN ('insert')) THEN
  NEW.alti_base = l.alti-NEW.base from log l where NEW.numlog = l.numlog ;
 return NEW ;
  END IF ;
 return NULL ;
END ;
$body$
language 'plpgsql' ;    

and the trigger :

CREATE TRIGGER trg_calcul_alti_us
AFTER insert
ON us
FOR EACH ROW
EXECUTE PROCEDURE activite.calcul_alti_us();

The result : alti_surface = 0 everywhere. I tried :

NEW.alti_base = (select l.alti-NEW.base from activite.log l where NEW.numlog =l.numlog) ;

I tried with a trigger BEFORE as well. Both failed. Thanks for help.

0

There are many errors:

  • It must be a BEFORE trigger to modify the row about to be inserted.

  • There is no column base in table log.

  • TG_OP is always in upper case, so you must compare with 'INSERT'.

0

Trigger after so I make an update on us instead of using TG_OP. Now it works as expected with this function :

create or replace function activite.calcul_alti_us()
returns trigger as
$body$
BEGIN
 update us
 set (alti_base, alti_sommet) = (l.alti - base, l.alti-sommet) 
 from log l 
 where us.numlog = l.numlog ;
 return NEW ;
END ;
$body$
language 'plpgsql' ;
  • The problem is here that, each time on insert in tbl log, the trigger is done on the full tbl us. But I would like the trigger to apply only to the rows inserted in tbl log. I tried ...where NEW.us.numlog = l.numlog but it failed. – Leehan Feb 13 at 10:19

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.