Hi,
I have a table 'Site' with the columns 'site_name', 'site_latitude', 'site_longitude', 'sitepoint_latlon'. The column 'sitepoint_latlon' is a geometry column. I want to implement a trigger that runs these procedure whenever an update on the site_longitude or site_latitude column is performed or new site record are added:
UPDATE site SET sitepoint_latlon = PointFromText('POINT(' || site_longitude || ' ' || site_latitude ||')',3395)
Basically what it does is to populate the sitepoint_latlon geometry column with values based on the latitude and longitude and the coordinate system (3395 = World Mercator).
I tried this script:
*****
CREATE OR REPLACE function sitepoint_conv() returns trigger AS
$$
BEGIN
If TG_OP = 'UPDATE' THEN
UPDATE site SET sitepoint_latlon = PointFromText('POINT(' || site_longitude || ' ' || site_latitude ||')',3395) WHERE site_nb = NEW.site_nb;
END IF;
RETURN NEW;
END;
$$ language plpgsql;
CREATE TRIGGER sitepoint_conv
AFTER INSERT OR UPDATE ON site
FOR EACH ROW EXECUTE PROCEDURE sitepoint_conv();
****
However the procedure fails, whenever I update or insert a new data. Whats wrong?
Thanks for any advice
Dante
Chat online and in real-time with friends and family!
Windows Live Messenger