I have 2 triggers on one table; one works for INSERTs :

CREATE TRIGGER "get_user_name"
AFTER INSERT ON "field_data"

This updates some values in the table.
And one for UPDATEs (to fill a history table):

CREATE TRIGGER "set_history"
BEFORE UPDATE ON "field_data"

The problem is that when I insert a new row in the table the procedure "add_info"() makes an update and therefore fires the second trigger, which ends with an error:

ERROR:  record "new" has no field "field1"

How can I avoid this?

  • 2
    It sounds like an error in the trigger logic. Could you show the trigger procedure that throws it? Jun 6, 2015 at 18:31
  • 1
    yes it does - what do you get if you just update a row? I'd expect the same error. Jun 6, 2015 at 18:46
  • You both are right: it was an error in my second trigger. My problem is solved but nevertheless I was thinking of something like the pg_trigger_depth() proposed by @Akash, which I need to test further now, because for the moment my trigger doesn't do anything at all when I use this test. Thanks to all of you !
    – dd_a
    Jun 7, 2015 at 21:36

3 Answers 3


(Obvious error in the trigger logic aside.)
In Postgres 9.2 or later, use the function pg_trigger_depth() that Akash already mentioned in a condition on the trigger itself (instead of the body of the trigger function), so that the trigger function is not even executed when called from another trigger (including itself - so also preventing loops).
This typically performs better and is simpler and cleaner:

CREATE TRIGGER set_history
WHEN (pg_trigger_depth() < 1)
EXECUTE FUNCTION gener_history();

In Postgres 10 or older use the keyword PROCEDURE instead of FUNCTION. See:

The expression pg_trigger_depth() < 1 is evaluated before the trigger function is entered. So it evaluates to 0 in the first call. When called from another trigger, the value is higher and the trigger function is not executed.

  • Thank you. Regarding the solution proposed by @Akash, as I said in a comment above, the test on pg_trigger_depth() = 0 didn't give me any result, but I found that preceding my code inside the body of the trigger function by "IF pg_trigger_depth() <> 1 THEN RETURN NEW; END IF;" as explained here : depesz.com/2012/02/01/waiting-for-9-2-trigger-depth does work. I'm mentioning this for future users, but your solution looks even simpler, and I will test it asap.
    – dd_a
    Jun 10, 2015 at 8:16
  • @dd_a: pg_trigger_depth() = 0 in Akash's answer makes no sense inside a trigger function. I added a comment there and more explanation to my answer. My solution should be simpler and faster for your use case than what Depesz presented in his blog. Jun 11, 2015 at 4:22
  • @G_Hosa_Phat: Please ask new questions as new questions. Comments are not the place. You can always link to this one for context. Sep 9, 2015 at 11:18
  • @ErwinBrandstetter: I apologize. I didn't think my questions worthy of a whole new thread as they were in the context of the current discussion. I will remove my comment and migrate the questions to a new thread. Sep 9, 2015 at 13:31

If you dont want the update trigger to be executed when the its called from within the insert trigger, you can surround your statements with a condition of pg_trigger_depth() which returns the depth, which wont be 0 when you are running the trigger directly/indirectly from another trigger.

So, within your function gener_history(), you can do something like this

IF pg_trigger_depth() = 1 THEN
.. your statements..

Here's another example: http://www.depesz.com/2012/02/01/waiting-for-9-2-trigger-depth/

  • It has to be noted that pg_trigger_depth() = 0 inside a trigger is always false because the minimum trigger depth is 1 there. You only get 0 outside of a trigger. Jun 10, 2015 at 18:20
  • right, seems I made a mistake, I've correct it, thanks
    – Akash
    Jun 11, 2015 at 12:08


Remove the AFTER INSERT trigger and call add_info from your app


Change the AFTER INSERT trigger into BEFORE INSERT

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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