+8If a execute this workspace:
Using an existing (and empty) database with this trigger on parcel table:
I get this error:
There seems to be a conflict between the "prepared statement" used by FME and the trigger. Are they incompatible?
Note: This project is a simplified version of another in which the database schema and the logic implemented by the trigger are considerably more complex.
Best answer by markatsafe
@Jorge Rosales After a little digging and research, we don't think it's an FME issue. Modifying the trigger SQL slightly makes the trigger behave. In SQLite, when inserting or updating data with a trigger, it seems you have to specify whether you are referencing the original record (OLD) or the update record(NEW). Adding this reference to the trigger SQL let's it run:
DROP TRIGGER "main"."parcel_insert";
CREATE TRIGGER parcel_insert
AFTER INSERT ON parcel
WHEN (NEW.code IS NULL)
BEGIN
SELECT RAISE(ABORT, 'Invalid parcel code');
ENDNote the addition of NEW to line 4:
WHEN (NEW.code IS NULL)
You should now only get an error if the replacement value for code (NEW.code) = <null>
I've attached a revised version of the workspace (FME 2021.2).
More details on SQLite triggers and the OLD | NEW references here
@Jorge Rosales Thanks for including the nice example workspace and test Geopackage. I was able to reproduce the ERROR. I'm not sure what is causing conflict between the table insert and the trigger. I've asked our development team to look at the issue and I'll report back.
@Jorge Rosales It looks like this issue arises in the underlying GDAL Geopackage driver FME uses for the the Geopackage format. We're trying to trace down source issue
+23From what I can tell FME doesn't like the "parcel_insert" trigger.
I you delete it (via DB Browser for SQLite) and validate "code" attribute via a transformer, FME is able to write in the table.
+8@Jorge Rosales It looks like this issue arises in the underlying GDAL Geopackage driver FME uses for the the Geopackage format. We're trying to trace down source issue
Thanks for the update @Mark Stoakes. If it is confirmed, I hope that the GDAL team fixs the error.
@Jorge Rosales After a little digging and research, we don't think it's an FME issue. Modifying the trigger SQL slightly makes the trigger behave. In SQLite, when inserting or updating data with a trigger, it seems you have to specify whether you are referencing the original record (OLD) or the update record(NEW). Adding this reference to the trigger SQL let's it run:
DROP TRIGGER "main"."parcel_insert";
CREATE TRIGGER parcel_insert
AFTER INSERT ON parcel
WHEN (NEW.code IS NULL)
BEGIN
SELECT RAISE(ABORT, 'Invalid parcel code');
ENDNote the addition of NEW to line 4:
WHEN (NEW.code IS NULL)
You should now only get an error if the replacement value for code (NEW.code) = <null>
I've attached a revised version of the workspace (FME 2021.2).
More details on SQLite triggers and the OLD | NEW references here
+8You are right. Thank you very much for the support @Mark Stoakes