create function and trigger to update column on table update
| От | Dara Olson |
|---|---|
| Тема | create function and trigger to update column on table update |
| Дата | |
| Msg-id | 5B6943440B4A4F16A1A77D0EA21E07B5@GISWKSTN2 обсуждение исходный текст |
| Ответы |
Re: create function and trigger to update column on table update
Re: create function and trigger to update column on table update |
| Список | pgsql-novice |
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
'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)));
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;
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();
FOR EACH ROW EXECUTE PROCEDURE update_catalog_number();
В списке pgsql-novice по дате отправления: