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?

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.

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/

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