Is it possible to discard insertion in specific condidions in HSQLDB?

I need to restrict insertion of new rows by certain conditions. The database have to throw an exception if insertable values mismatch certain condition. First I've tried to use CONSTRAINT object, but got the following error: "SQLException: feature not supported: subquery in check constraint in statement".

Then I tried to use the trigger:

CREATE TRIGGER tg_val BEFORE INSERT ON valuta REFERENCING NEW ROW AS new FOR EACH ROW WHEN (new.simvol IS NULL) SIGNAL SQL_STATE '45000'

And got another error: SQLException: unexpected token: SI开发者_Go百科GNAL.

---

The error probably indicates the version of HSQLDB used does not support the SIGNAL statement in this context. HSQLDB 2.0.1 (at the moment RC2) supports this, with SQLSTATE (not SQL_STATE as currently mentioned in the Guide) :

CREATE TRIGGER tg_val BEFORE INSERT ON valuta REFERENCING NEW ROW AS new FOR EACH ROW WHEN (new.simvol IS NULL) SIGNAL SQLSTATE '45000'

It is clear from your attempt to use a check constraint with subquery that you want to use a more complex check condition than a NOT NULL constraint. In this case, use a BEGIN ... END block for the trigger in order to perform any SELECT statement from different tables (including the target table) to verify your condition.