Is trigger procedure supported in HSQLDB 2.4.0

Question

I'm using postgresql dialect with HSQLDB 2.4.0 on a test context. I'm also using a trigger procedure

When I load my first SQL script into HSQLDB:

SET DATABASE SQL SYNTAX PGS TRUE;

CREATE FUNCTION trigg_proc() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO test(desc)
  VALUES('hello world');
  RETURN NEW;
END;
$$;

CREATE TABLE test(
desc CHARACTER VARYING(60) NOT NULL
);

I get this error from HSQLDB

...
Caused by: java.sql.SQLSyntaxErrorException: unexpected token: TRIGGER
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source)
    at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:473)
    ... 58 more
Caused by: org.hsqldb.HsqlException: unexpected token: TRIGGER
    at org.hsqldb.error.Error.parseError(Unknown Source)
    at org.hsqldb.ParserBase.unexpectedToken(Unknown Source)
    at org.hsqldb.ParserBase.checkIsNonCoreReservedIdentifier(Unknown Source)
    at org.hsqldb.ParserDQL.checkIsSchemaObjectName(Unknown Source)
    at org.hsqldb.ParserDQL.readTypeDefinition(Unknown Source)
    at org.hsqldb.ParserRoutine.readProcedureOrFunctionDeclaration(Unknown Source)
    at org.hsqldb.ParserRoutine.readCreateProcedureOrFunction(Unknown Source)
    at org.hsqldb.ParserRoutine.compileCreateProcedureOrFunction(Unknown Source)
    at org.hsqldb.ParserDDL.compileCreate(Unknown Source)
    at org.hsqldb.ParserCommand.compilePart(Unknown Source)
    at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
    at org.hsqldb.Session.executeDirectStatement(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    ... 61 more

I would like to know if I'm missing something about the configuration or if HSQLDB does not support trigger procedure.

Answer 1

Syntax of triggers in postgres and HSQLDB are not compatible.

For example if you need to creating triggers in HSQLDB, you will write it as something like below

CREATE TRIGGER my_table_trigger 
    BEFORE INSERT ON my_table REFERENCING NEW ROW AS NEW
    FOR EACH ROW 
  BEGIN ATOMIC
      BEGIN ATOMIC
    IF NEW.id IS NULL THEN
      SELECT my_table_seq.NEXTVAL INTO NEW.id FROM DUAL;
    END IF;
  END;
END

Which is quite similar to Oracle trigger syntax.

Database dialects usually didn't help to perform cross DB PL/SQL operations.

You should keep this point in your mind.

Wherever you need to perform any database specific functionality , you shouldn't rely on any database dialect but use native queries instead.

Also maintain different native queries for each database you want to use in your application.