I am new to creating triggers/functions. I am trying to create a trigger and function that when a specific table is updated or records added that it updates an existing column (catalog_number) from an existing column and latitude/longitude. Below is what I have so far. Does anyone know what I am doing wrong or if I am going in the complete wrong direction? Is there a way to define that the column only gets updated on the records that were updated or inserted. Any help would be greatly appreciated!
Thanks!
Dara
CREATE OR REPLACE FUNCTION invasive_species.update_catalog_number() RETURNS TRIGGER AS
'BEGIN
IF TG_OP = "UPDATE" THEN
UPDATE invasive_species.invspp_occurrence_data
SET catalog_number = "tsn_char" || $_$ || x(centroid(transform(the_geom, 4326)))|| $_$ || y(centroid(transform(the_geom, 4326)));
RETURN NEW;
END IF;
RETURN NULL;
END;'
LANGUAGE plpgsql;
CREATE TRIGGER catalog_num_trigger AFTER UPDATE ON invasive_species.invspp_occurrence_data
FOR EACH ROW EXECUTE PROCEDURE update_catalog_number();