How to translate SQL trigger to liquibase

Question

Im trying to migrate our current database to use liquibase but I dont understand how to translate the following into something that liquibase can use.

--liquibase formatted sql

--changeset usr:33 splitStatements:false dbms:oracle failOnError:true
create trigger some_trigger_id before insert
on table1 for each row
declare
    seq_val number;
begin
    if :new.myid is null then
      select seq_myseq.nextval
      into :new.myid
      from dual;
    else
      begin
        select seq_myseq.currval
        into myid
        from dual;
      exception
        when others then
          raise_application_error(-20000, 'Illegal!! ...');
      end;

      if :new.myid > seq_val then
        raise_application_error(-20000, 'Illegal!! ...');
      end if;
    end if;
end;
/  

I have tried putting this code in a seperate file and including it with <sqlFile> but it fails with the message.

"Encountered the symbol end of file when when expecting one of the following ;"

When i check oracle there is a trigger created but the code is cut of and looks like this:

create or replace trigger some_trigger_id before insert
on table1 for each row
declare
    seq_val number

The code has gotten cut of at ;, it is as if the attribute splitStatements is ignored, also it does not matter if I replace ; with some other symbol i get the same error???

Update: This is the exact error im getting.

SEVERE 2015-06-25 08:21: liquibase: src/main/resources/db-schema.xml: src/main/resources/db-schema.xml::1435144676490-15::a187001: Change Set src/main/resources/d
b-schema.xml::1435144676490-15::a187001 failed.  Error: ORA-06550: line 5, column 15:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   ;
 [Failed SQL: begin
    if :new.myid is null then
      select seq_myseq.nextval
      into :new.myid
      from dual]
liquibase.exception.DatabaseException: ORA-06550: line 5, column 15:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   ;
 [Failed SQL: begin
    if :new.myid is null then
      select seq_myseq.nextval
      into :new.myid
      from dual]
        at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:316)
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55)
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:122)
        at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1243)
        at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1226)
        at liquibase.changelog.ChangeSet.execute(ChangeSet.java:548)
        at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:51)
        at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:73)
        at liquibase.Liquibase.update(Liquibase.java:208)
        at liquibase.Liquibase.update(Liquibase.java:188)
        at liquibase.Liquibase.update(Liquibase.java:323)
        at org.liquibase.maven.plugins.LiquibaseUpdate.doUpdate(LiquibaseUpdate.java:33)
        at org.liquibase.maven.plugins.AbstractLiquibaseUpdateMojo.performLiquibaseTask(AbstractLiquibaseUpdateMojo.java:30)
        at org.liquibase.maven.plugins.AbstractLiquibaseMojo.execute(AbstractLiquibaseMojo.java:394)
        at org.apache.maven.plugin.DefaultBuildPluginManager.executeMojo(DefaultBuildPluginManager.java:133)
        at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:208)
        at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:153)
        at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:145)
        at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:108)
        at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:76)
        at org.apache.maven.lifecycle.internal.builder.singlethreaded.SingleThreadedBuilder.build(SingleThreadedBuilder.java:51)
        at org.apache.maven.lifecycle.internal.LifecycleStarter.execute(LifecycleStarter.java:116)
        at org.apache.maven.DefaultMaven.doExecute(DefaultMaven.java:361)
        at org.apache.maven.DefaultMaven.execute(DefaultMaven.java:155)
        at org.apache.maven.cli.MavenCli.execute(MavenCli.java:584)
        at org.apache.maven.cli.MavenCli.doMain(MavenCli.java:213)
        at org.apache.maven.cli.MavenCli.main(MavenCli.java:157)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:497)
        at org.codehaus.plexus.classworlds.launcher.Launcher.launchEnhanced(Launcher.java:289)
        at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:229)
        at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:415)
        at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:356)
Caused by: java.sql.SQLException: ORA-06550: line 5, column 15:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   ;

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:194)
        at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1000)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)
        at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1882)
        at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1847)
        at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:301)
        at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:314)
        ... 34 more

Solution: The solution was to expicitly state endDelimiter \n;

Solution 1

Use custom endDelimiter (default is semicolon, so liquibase is trying to split your code into incorrect parts).

<changeSet id="1" author="me">
    <sql endDelimiter="/">
        create trigger some_trigger_id before insert
        on table1 for each row
        declare
            seq_val number;
        begin
            if :new.myid is null then
              select seq_myseq.nextval
              into :new.myid
              from dual;
            else
              begin
                select seq_myseq.currval
                into myid
                from dual;
              exception
                when others then
                  raise_application_error(-20000, 'Illegal!! ...');
              end;

              if :new.myid > seq_val then
                raise_application_error(-20000, 'Illegal!! ...');
              end if;
            end if;
        end;
        / 
    </sql>
</changeSet>

Solution 2

Check out this page http://www.liquibase.org/documentation/sql_format.html for changelogs in SQL format.

You need to ensure that you have the changeset header and be sure to set splitStatements:false

Comments

[kinjelom]

I think that "select seq_myseq.currval" may return value taken in concurrent session... so you should not overwrite the value of :new.myid if it is not null... Am I right?

[kinjelom]

Does my answer solve your problem? Please let us know if you have any other questions.

[user3139545]

I have updated my question with infro from your suggestion.

[Nathan Voxland]

splitStatements:false should fix your problem, it may be an bug in the version of Liquibase you are using. I tested your example against the 3.4.0 code I'm going to release later today and it seemed to work. Can you try that version when it comes out?

[user3139545]

Im running the latest version now. Still the same problem. I have included the exact error in OP.

[user3139545]

The solution was to explicitly state endDelimiter="\n;"