Updatable view using triggers

[Caketray]

I’m using Libreoffice 7.1.5.2 and HSQLDB 2.3.2, in the classic split database setup.

I’ve created a view that aggregates data from several tables, both for easy reference and because I was having trouble getting complex selections to work in forms. Data retrieval here works fine, the form displays as expected. However, I would like to also be able to insert or edit entries shown in the form like I can with real tables. I created several triggers, causing updates to the view to update data in the corresponding table (my view filters based on data from several tables, but inserts and updates only need to affect records in one table).

This now works as expected from SQL: INSERT commands on the view will propagate correctly to the source table. However the table in Base still has all editing controls greyed out and doesn’t seem to know that editing is possible now. Is there a way to get Base to recognize that a view is not actually read-only?

[RobertG]

Base doesn’t analyze SQL of a view. So it will only show the content of the view, but won’t change it. This is one of the limitations in the GUI.

One other limitation is: Base only will allow to change data in a table, if there is a primary key and the database uses primary keys for table. So you could create a table and add data to it without primary key in HSQLDB, but the GUI will show this table as write protected and the form won’t change this data.

You have to create queries in the GUI to get a kind of view, where you could add data. This SQL-code will be analyzed by Base - but triggers won’t work here.