Create a Trigger to send ODK Data to a table

Hi,

I'm trying to create a trigger that will transfer specific data into another table - where I will refer my analytics.

But whenever I define a trigger, a submission error in ODK collect occurs.

Please help, thank you in advance!
Jonathan

Hi Jonathan,

I think you use PostgreSQL. I use triggers to populate work tables from ODK collected data. It works really really fine. Did you check the database's logs ?

The trigger acts AFTER each insert statement on my xxx_CORE table :

CREATE TRIGGER alimente_saisie_obs
AFTER INSERT
ON odk_prod."EXO_MOBILE_8_CORE"
FOR EACH STATEMENT
EXECUTE PROCEDURE odk_prod.alimente_saisie_observation();

Maybe you have an error with your function and maybe you call the trigger before insert, so the thrown error forbid Aggregate to commit the insert. So ODKcollect shows an error when you submit your forms.

Hi!

My trigger seems fine and tested in to a standalone database.

Yes, my trigger is AFTER insert method and there's no new log on the database.

Here's my trigger script.

CREATE DEFINER=xxxxx@localhost TRIGGER xxxxx.trgPAP_PROFILE AFTER INSERT ON xxxxx.PAP_TEMP FOR EACH ROW
BEGIN

INSERT INTO `PAP_PROFILE`
(
    `Educo_ID`,
    `Firstname`,
    `Middlename`,
    `Lastname`,
    `Municipality`,
    `Gender`
)

SELECT
concat(date_format(now(),'%Y'),'-',lpad(NEW.`PAP_TEMP`.`ID`,3,'0')) AS `EducoID`,
NEW.`PAP_TEMP`.`FirstName` AS `FirstName`,
NEW.`PAP_TEMP`.`MiddleName` AS `MiddleName`,
NEW.`PAP_TEMP`.`MiddleName` AS `LastName`,
NEW.`PAP_TEMP`.`Municipality` AS `Municipality`,
NEW.`PAP_TEMP`.`Gender` AS `Gender`;

END;

Hello,

There is likely to be a log file on a similar path to this ....\Program Files\PostgreSQL\9.4\data\pg_log (depending on your PostgeSQL version which has a file name of this format postgresql-2018-04-24_000000.log. This log should give you more information. In my experience, this is usually related to user permissions. We have resolved this previously by making the owner of the schema a super user. I f you could give more information on the error, it would be helpful.

You shoukd also use the DECURITY DEFINER clause in you function definition : "SECURITY DEFINER specifies that the function is to be executed with the privileges of the user that created it."

Sorry Jonathan, I did not understand that you are using Mysql.
So It will be more difficult for me to help you.

thank you mathieubossaert! I think the only resolution will be creating the owner of schema as a super user.

Hi Paul thank you for this, i think you are right need to upgrade schema owner's permission.