CODEWITHSUNDEEP
postgresqltriggersauto-update
Lisdengard
Lisdengard

Reputation: 139

Postgres - ´after update´ trigger doesn't fire

I have this table for which one of the columns is to be filled with values from other columns of the same table (these other columns represent a taxonomic hierarchy whose lowest level I wish to store in this other column).

To achieve this I implemented the following trigger:

CREATE OR REPLACE FUNCTION get_taxon()
RETURNS TRIGGER LANGUAGE plpgsql AS
$BODY$
   BEGIN
    UPDATE taxon SET taxon = coalesce(subespecie, especie, genero, subfamilia, familia, infraordem, subordem, ordem, superordem, subclasse, classe, subphylum, phylum, reino )
    WHERE taxon IS NULL;
    RETURN NEW;
   END
$BODY$
VOLATILE; 

CREATE TRIGGER update_taxon
    AFTER INSERT OR UPDATE ON taxon
    FOR EACH STATEMENT  
    WHEN (pg_trigger_depth() = 0) -- Prevent recursive trigger calls
    EXECUTE PROCEDURE get_taxon();

When I insert a new record the trigger works as expected, but if I update an existing record, nothing happens - the trigger just ignores UPDATE operations and I don't know why.

Can anyone shed some light on this please?

Upvotes: 1

Views: 1338

Answers (1)

Patrick
Patrick

Reputation: 32161

After your statement trigger first fired all records in the table should have their taxon field updated with the best available information. When you update a record you may want to update the taxon value, but you are better off with a BEFORE INSERT OR UPDATE FOR EACH ROW trigger. The only new data is contained in the row for which the trigger fires, of course. So try this:

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();

Upvotes: 1

Related Questions