Postgresql – How to prevent a PostgreSQL trigger from being fired by another trigger

plpgsqlpostgresqlpostgresql-9.3trigger

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?

Best Answer

^{(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 PROCEDURE gener_history();

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.

Related Solutions

Postgresql – How to specify trigger execution order under PostgreSQL

I couldn't say it better than the manual does here:

If any BEFORE or INSTEAD OF trigger returns NULL, the operation is abandoned for that row and subsequent triggers are not fired (for that row).

Neither the (alphabetically) later triggers on the same event nor any AFTER triggers will fire, if the BEFORE trigger cancels.

Mysql – How to speed up audit trigger

You could alter the trigger with brute force INSERTs as follows:

CREATE TRIGGER trigger1 
    AFTER UPDATE 
    ON tbl1 
    FOR EACH ROW 
BEGIN 
  DECLARE insert_style INT;

  -- I'm interested in two fields only and if they were changed only 

  SET insert_style = 0;
  IF new.y != old.y THEN 
      SET insert_style = 1;
  END IF;
  IF new.country != old.country THEN 
      SET insert_style = insert_style + 2;
  END IF; 

  CASE insert_style
      WHEN 1 THEN
          insert into z_lot values
          ('','tbl1',new.id,'y',old.y,new.y);
      WHEN 2 THEN
          insert into z_lot values
          ('','tbl1',new.id,'country',old.country,new.country);
      WHEN 3 THEN
          insert into z_lot values
          ('','tbl1',new.id,'y',old.y,new.y),
          ('','tbl1',new.id,'country',old.country,new.country);
  END CASE;      

END

Best Answer

Related Solutions

Postgresql – How to specify trigger execution order under PostgreSQL

Mysql – How to speed up audit trigger

Related Question