Trigger Syntax on HsqlDB : expected ;
===

I use hsqldb for my unit tests. My production use Oracle 11G Db.
When i run my start script as above:

    <jdbc:embedded-database id="dataSource" type="HSQL">
    </jdbc:embedded-database>
    	
    <jdbc:initialize-database data-source="dataSource" ignore-failures="DROPS">
      <jdbc:script location="classpath:/sql/init-cct-schema.sql" separator=";" />
      <jdbc:script location="classpath:/sql/init-cct-insert.sql" separator=";" />
    </jdbc:initialize-database>

I am really quite the trigger example in [HSQL docs][1].

I see [this post][2]:
But his solution doesn't work for me, or I don't understand it.

I have always this error:

    java.lang.IllegalStateException: Failed to load ApplicationContext
    	at org.springframework.test.context.TestContext.getApplicationContext(TestContext.java:157)
    	at org.springframework.test.context.support.DependencyInjectionTestExecutionListener.injectDependencies(DependencyInjectionTestExecutionListener.java:109)
    	at 
    ...
    	... 38 more
    Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement at line 9 of resource class path resource [sql/init-cct-schema.sql]:  CREATE TRIGGER TI_TYPE_MVT BEFORE INSERT ON TYPE_MVT     REFERENCING NEW AS newrow FOR EACH ROW     BEGIN ATOMIC       IF newrow.TYPE_MVT_PK is null THEN         SET newrow.TYPE_MVT_PK = SQ_TYPE_MVT.nextval
    	at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.executeSqlScript(ResourceDatabasePopulator.java:199)
    	at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.populate(ResourceDatabasePopulator.java:132)
    	at org.springframework.jdbc.datasource.init.CompositeDatabasePopulator.populate(CompositeDatabasePopulator.java:55)
    	at org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:45)
    	... 41 more
    Caused by: java.sql.SQLSyntaxErrorException: unexpected end of statement:  required: ;
    	at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
    	at org.hsqldb.jdbc.Util.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.ResourceDatabasePopulator.executeSqlScript(ResourceDatabasePopulator.java:184)
    	... 44 more
    Caused by: org.hsqldb.HsqlException: unexpected end of statement:  required: ;
    	at org.hsqldb.error.Error.parseError(Unknown Source)

Here is my trigger:

    SET DATABASE SQL SYNTAX ORA TRUE;    
    CREATE TRIGGER TI_TYPE_MVT BEFORE INSERT ON TYPE_MVT
            REFERENCING NEW AS newrow FOR EACH ROW
            BEGIN ATOMIC
              IF newrow.TYPE_MVT_PK is null THEN
                SET newrow.TYPE_MVT_PK = SQ_TYPE_MVT.nextval;
              END IF;
            END;

I try without the final ';' , it's continue to fail.

Here is my dependancy on HSQL:

    <dependency>
      <groupId>org.hsqldb</groupId>
      <artifactId>hsqldb</artifactId>
      <version>2.2.8</version>
    </dependency>


any ideas?

  [1]: http://hsqldb.org/doc/guide/triggers-chapt.html
  [2]: http://stackoverflow.com/questions/9157027/hsql-create-procedure-syntax-doesnt-seem-to-match-the-documentation
