title: Coalesce in PostgreSQL trigger does not fire on update
body: <p>In the context of a database for a paleontological collection, I have this table definition in PostgreSQL v.12:</p>
  
  <pre><code>CREATE TABLE taxon (
  id                  integer  DEFAULT NEXTVAL('taxon_oid_seq')   PRIMARY KEY,
  taxon                varchar(100)         UNIQUE  NOT NULL,
  reino                varchar(50)                  NOT NULL,
  phylum               varchar(100)         ,
  subphylum            varchar(100)         ,
  classe               varchar(100)         ,
  subclasse            varchar(100)         ,
  superordem           varchar(100)         ,
  ordem                varchar(100)         ,
  subordem             varchar(100)         ,
  infraordem           varchar(100)         ,
  familia              varchar(100)         ,
  subfamilia           varchar(100)         ,
  genero               varchar(100)         ,
  especie              varchar(100)         ,
  subespecie           varchar(100)             );
  </code></pre>
  
  <p>The taxon field is to be automatically filled with the lowest level to which it was possible to determine a given species taxonomy. In order to achieve that I have this trigger:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION get_taxon() RETURNS TRIGGER LANGUAGE
  plpgsql AS $BODY$ 
  BEGIN   
  NEW.taxon := coalesce(NEW.subespecie, NEW.especie, NEW.genero, NEW.subfamilia, 
                        NEW.familia, NEW.infraordem, NEW.subordem, NEW.ordem, NEW.superordem,
                        NEW.subclasse, NEW.classe, NEW.subphylum, NEW.phylum, NEW.reino);   
  RETURN NEW; 
  END; 
  $BODY$ 
  VOLATILE; 
  
  CREATE TRIGGER update_taxon
  BEFORE INSERT OR UPDATE ON taxon
  FOR EACH ROW EXECUTE PROCEDURE get_taxon();
  </code></pre>
  
  <p>However this trigger only fires on <code>INSERT</code>, nothing happens if an <code>UPDATE</code> is made. How can have this trigger also firing in the case of an <code>UPDATE</code>?</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>I just realized that I have another trigger that fires on update. This other trigger is defined as: </p>
  
  <pre><code>CREATE OR REPLACE FUNCTION taxon_history_update() RETURNS trigger AS 
      $BODY$
      BEGIN
              INSERT INTO history.taxon(operacao, data, tecnico, original_oid, 
                                        taxon, reino, phylum, subphylum, classe, 
                                        subclasse, superordem, ordem, subordem, 
                                        infraordem, familia, subfamilia, genero, 
                                        especie, subespecie) 
                                  VALUES        
                                        ('UPDATE', current_timestamp, current_user, 
                                         old.oid, old.taxon, old.reino, old.phylum, 
                                         old.subphylum, old.classe, old.subclasse, 
                                         old.superordem, old.ordem, old.subordem, 
                                         old.infraordem, old.familia, 
                                         old.subfamilia, old.genero, old.especie, 
                                         old.subespecie);
                                         RETURN old;
      END;
      $BODY$
      LANGUAGE plpgsql;
  
  CREATE TRIGGER taxon_history_update
  BEFORE UPDATE ON taxon
  FOR EACH ROW EXECUTE PROCEDURE taxon_history_update();
  </code></pre>
  
  <p>Could it be there is some kind of conflict/interferance bewteen these two triggers? My <code>update_taxon</code> trigger works also for updates provided I drop the <code>taxon_history_update</code> trigger!</p>