How to prevent a PostgreSQL trigger from being fired by another trigger?

Question

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

CREATE TRIGGER "get_user_name"
AFTER INSERT ON "field_data"
FOR EACH ROW EXECUTE PROCEDURE "add_info"();

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"
FOR EACH ROW EXECUTE PROCEDURE "gener_history"();

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?

• András Váczi: It sounds like an error in the trigger logic. Could you show the trigger procedure that throws it?
• Jack Douglas: yes it does - what do you get if you just update a row? I'd expect the same error.
• dd_a: 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 !

Answer 1

^{(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
BEFORE UPDATE ON field_data
FOR EACH ROW 
WHEN (pg_trigger_depth() < 1)
EXECUTE FUNCTION gener_history();

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

• Trigger uses a procedure or a function?

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.

• dd_a: 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.
• Erwin Brandstetter: @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.
• Erwin Brandstetter: @G_Hosa_Phat: Please ask new questions as new questions. Comments are not the place. You can always link to this one for context.
• G_Hosa_Phat: @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.

Answer 2

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..
END IF;

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

• Erwin Brandstetter: 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.
• Akash: right, seems I made a mistake, I've correct it, thanks

Answer 3

SUGGESTION #1

Remove the AFTER INSERT trigger and call add_info from your app

SUGGESTION #2

Change the AFTER INSERT trigger into BEFORE INSERT