Trigger for historical

[yockee]

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

[VinceAngelo]

I've done this in PG13, PG14, and PG15 with no difficulty.  Except that one time when  I didn't grant access to the sequence in the target table to the role as which the inserts were running.  Then INSERTs silently failed.  I only found the problem by connecting to pgAdmin as the insert script user, generating an appropriate INSERT statement, and getting a useful error message.

- V 

[yockee]

The trigger / function works fine if I use "INSERT" or "UPDATE" statement from pgadmin (actually, I am using the trigger for UPDATE event only).
But the function failed to execute whenever there is update from Field Map or Survey123. Survey123 throws error. As soon as I disable the trigger, Survey123 works normal again.

I just want to record objectid or globalid along with date field to another nonspatial table. I don't need to record any spatial attributes.

Another question, where can I see the log for Survey123 ? Is it via Server Manager ?

Thanks

[VinceAngelo]

Well, you certainly shouldn't be connecting a web app to a table as the table owner (huge security risk), so you need to make sure that the trigger works when logged in as the survey user. If the table is versioned, you won't see the UPDATEs until the version is posted to the business table (and detecting UPDATE events becomes problematic, since the Adds and Deletes tables both change on an update.

You'd probably be better off adding Editor Tracking on the table, then using a SQL batch job to LEFT OUTER JOIN to the survey table, driving an INSERT into the id tracking table.

- V

[yockee]

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

[VinceAngelo]

---

@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]

---

@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 ?