Trigger for historical

Question

I want to record an insert or update events happening on one spatial table into another table (non spatial) for simple historical purpose.

Say for example I have 2 tables SpatialA (date1 DATE, guid VARCHAR38, objectid INT, col1 VARCHAR5 ,shape) and NonSpatialB (date1 DATE, guid VARCHAR38, objectid INT, col1 VARCHAR5).

I have trigger on SpatialA table that executes function :

CREATE OR REPLACE FUNCTION function_copy() RETURNS TRIGGER AS
$BODY$
BEGIN
INSERT INTO
schema1.NonSpatialB (date1 ,*objectid*)                                                                                          //schema1.NonSpatialB (date1 ,*GUID*) -->  I also tried using "GUID" as well
VALUES(new.date1,new.objectid);
RETURN new;
END;
$BODY$
language plpgsql;

CREATE TRIGGER trig_copy
AFTER INSERT ON schema1.SpatialA
FOR EACH ROW
EXECUTE PROCEDURE function_copy();

The data on SpatialA are updated via Survey123 or FieldMap.

When the trigger is enabled, I  CANNOT insert nor update a record on SpatialA. The trigger prevents the update or insert event from happening.

How do I suppose to write the function so that I can insert  into another table?

Note: those trigger and function work perfectly when I do insert or update on non spatial table

I am using Postgresql13, Arcgis Enterprise 11.0

yockee · Answer

@VinceAngelo wrote:@yockee wrote:The survey user is portal user and it does not have any connection with database user. Am I wrong ?

The map service should NOT be published using table owner authentication. Which means that you need to validate your trigger function in pgAdmin while logged in as the publishing user.

- V

The map service should NOT be published using table owner authentication.  --> Ok. Noted. Thanks for the advice.

Which means that you need to validate your trigger function in pgAdmin while logged in as the publishing user.  --> OK, noted..  Question is, with the same setup, why does the trigger only work when it does not involve any GUID or ObectID recording ? Trigger works fine if I want to record just the date of data when it is entried, which means it does not involve any recording of GUID nor ObjectID.  
I think the problem is that my function does not have enough information about parameters that are passed to it. I don't have any idea how the data is updated in Field Map or Survey123, what are the parameters fed into them? How can they (Field Map / Survey123) know which record is updated ?

VinceAngelo · Answer

@yockee wrote:The survey user is portal user and it does not have any connection with database user. Am I wrong ?

The Portal user has nothing to do with the connection that was used to make the map service.

The map service should NOT be published using table owner authentication. Which means that you need to validate your trigger function in pgAdmin while logged in as the publishing user.

- V

yockee · Answer

Thanks @VinceAngelo .

The survey user is portal user and it does not have any connection with database user. Am I wrong ?

I have been using a different database trigger in the previous project with the same setup. for example, a trigger to fill in a coloumn wherenever there is an insert / update on another coloumn.

Checkout the function below. It works just fine eventhough the table is updated via Survey123 or FieldMap. I think its because it does not involve recording of any OLD OID nor OLD GUID. It merely update new data into a coloumn without needing to know about the OID nor GUID  :

CREATE TRIGGER xxupd_building_utility
BEFORE INSERT OR UPDATE
ON owner_gis.bnp_survey
FOR EACH ROW
EXECUTE FUNCTION public.xupd_building();

CREATE OR REPLACE FUNCTION public.xupd_building()
RETURNS trigger
LANGUAGE 'plpgsql' COST 100 VOLATILE NOT LEAKPROOF
AS $BODY$

BEGIN
IF new.building_customer <> 'YES' THEN
new.building_function := (CAST(coalesce(new.col1, '0') AS integer)+CAST(coalesce(new.col2, '0') AS integer)+CAST(coalesce(new.col3, '0') AS integer));
END IF;

RETURN new;
END;
$BODY$;

ALTER FUNCTION public.xupd_building()
OWNER TO postgres;

The difference with what I am trying to do is in the recording of OID or GUID. I just have to find a way to know which data with corresponding OID or GUID is changing