Trigger (before update) not working. It works in a local PostGIS installation with same table structure and data

Question

A before insert/update trigger on one of my CartoDB tables is not being raised. Same trigger is working in a local installation of PostGIS.

This is my source table. And here, the trigger's code:

CREATE OR REPLACE FUNCTION update_geom_column()
RETURNS trigger AS
$BODY$
DECLARE
  coords_array varchar[] := ARRAY[2];
BEGIN
  if new.map_url is null then
    raise exception 'map_url cannot be null';
  end if;

  -- Get coords using regexp_matches
  select regexp_matches(NEW.map_url, 'center=(-?[\d]*\.[\d]*),(-?[\d]*\.[\d]*)&') into coords_array;
  NEW.the_geom = CDB_LatLng(coords_array[1]::numeric, coords_array[2]::numeric);

  return NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

drop trigger if exists update_geom_column_trigger on foursquare_checkins;
CREATE TRIGGER update_geom_column_trigger
BEFORE INSERT OR UPDATE
ON foursquare_checkins
FOR EACH ROW
EXECUTE PROCEDURE update_geom_column();

Every time there's a new insertion in my CartoDB table, all geometry fields of all rows are set to null. But the same trigger is working in my local PostGIS.

May it be something related with CartoDB itself?

Answer 1

It seems that your table is a Sync Table. Unfortunately, on each sync, the triggers of the sync tables are dropped.

Time ago we reported this into the CartoDB repo for keeping triggers through synchronizations, but it seems that the team hasn't worked on that yet: https://github.com/CartoDB/cartodb/issues/1941