CODEWITHSUNDEEP
postgresqltriggersplpgsqlpostgresql-10
caleb baker
caleb baker

Reputation: 144

After insert trigger not giving desired result

I have a trigger on a table called pohead

CREATE TRIGGER poautorelease
  AFTER INSERT OR UPDATE 
  ON public.pohead
  FOR EACH ROW
  EXECUTE PROCEDURE public._poautorelease();

which fires the function

BEGIN
 IF (TG_OP = 'UPDATE' AND NEW.pohead_printed = TRUE) 
 OR (TG_OP = 'INSERT' AND NEW.pohead_printed = TRUE) THEN
   PERFORM releasepurchaseorder(NEW.pohead_id);
 END IF;
 RETURN NEW;
END

It seems that on an UPDATE the function releasepurchaseorder() is performed and on the INSERT it is not. Am I doing anything wrong in code? Or do I need to look elsewhere for the problem.

I have seen some post detailing a FOR EACH ROW. but there is only ever one row per update or insert. I think I may be using the NEW variable incorrectly.

The other possibility that I might consider if the code is correct is perhaps they disable triggers before they insert? I don't know if this is possible but I assume it must be.

Upvotes: 0

Views: 50

Answers (1)

jjanes
jjanes

Reputation: 44383

If I create a tattle-tale function, it works for me on both insert and update.

create function releasepurchaseorder(int) returns void language plpgsql as $$ 
BEGIN
    RAISE NOTICE 'asfdasfda'; 
END;
$$;

Provided of course that pohead_printed is in fact true.

So if you think there really is a problem, then please describe how you decided it is not firing, and show a full reproduction case (including the complete definition of the table, and of both functions). Of course you can simplify both the table and the functions, but verify the simplified versions still exhibit the behavior you are describing.

Upvotes: 1

Related Questions