(This question is also exposed here:
https://stackoverflow.com/questions/60117123/coalesce-in-postgresql-trigger-does-not-fire-on-upddate )
Hi Everyone!
In the context of a database for a paleontological collection, I have this table definition:
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) );
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:
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();
However this trigger only fires on INSERT
, nothing happens if an UPDATE
is made. How can have this trigger also firing in the case of an UPDATE
?
Thanks,