How to get SQL TRIGGER to display SQLSTATE message text in a Base form?

[patpend]

I’m trying to display a message box in an HSQLDB trigger block within a LibreOffice Base form and can’t get it working.

I have a LO Base app using a split HSQLDB 2.3.2. It includes a table “checks” that has a boolean column “issued”. When issued is “true” I want to prevent deletion of that row. So I created the following trigger to detect this condition and prevent deletion:

CREATE TRIGGER "delete_check" BEFORE DELETE ON "checks"
REFERENCING OLD AS oldrow

FOR EACH ROW WHEN (oldrow."issued" = true)
BEGIN ATOMIC
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Issued check cannot be deleted';
END;

When I use the built-in grid view and try to delete a row with the issued flag set, the message box DOES pop up as expected.

But running in a Base form and trying to delete the row using the navigation bar, the message box does not pop up. (In either case the row is not deleted, which is good because it means the trigger is working.)

How can I get the message box to display from an HSQLDB trigger block within an LibreOffice Base form?

[Ratslinger]

Hello,

Have just tested and it does & doesn’t work.

There are two navigation bars available. One is from menu View->Toolbars and the other is a control in Base. The control in Base works as you want:

Deleting from the ‘Toolbar’ navigation bar does not generate the message and once tried it also negates the display of the message from the navigation control. This does start working again once the form is closed & opened again.

This is a bug and you should report on Bugzilla.

Beside using a trigger for this, you could also write a small macro attached to the Confirm deletion event of the internal form. See → Events.

With both items in place you will always get a message.

[patpend]

Thank you. BTW I could not get the form navigation control to work until I quit and restarted LO, even without first trying to delete using the toolbar navigation bar.

[drewjensen]

Ah, saw the opened issue and just confirmed it. Also attached an embedded firebird database which implements your example. In this case I used the very latest 6.3 Alpha build of LO and the one difference, is that while the Form Navigation Delete toolbutton does not display the error message it does not seem to mess up the grid navigation either. Oddly, if you try to delete an issued check record, using the form, by hitting the delete key then the error dialog is displayed - but either way with the error dialog or not, the record is not deleted.