Potential problems on creating Oracle Triggers/Procedures in Liquibase (changesets sql)

May 24, 2012 java sql oracle liquibase english
Share on:

My case:

I have been using a recent version of liquibase (2.0.5). For various reasons and mostly simplicity, in terms of moving straight away from the model of keeping my own SQL statements and applying them manually,to the context of Liquibase and it's automation, I have been using the formated sql changeset/log support.

I am quite satisfied with the overall support and functionality, that gives me time and space to explore the scenario of migrating to a pure XML based changeset format in the near future, as Liquibase suggests. Currently I had a problem, trying to include an sql formatted changeset that was creating Triggers (Oracle specific). I don't like Triggers, they are evil but that is another story. I had to include from an existing DB functionality some triggers.

Eventually you could find many other people with the same formatting sql problem, and the line delimiter Trigger syntax etc. Example here.

Potential Solutions (for the time being).

  1. You can add separate sql formatted changelog files (.sql) containing in each change a single Trigger, that would make liquibase cope with the end line delimiters and the way it has to read the trigger statement. (I did not like it, since I needed to create many separate files and increase my change id counter). I have also, experimented with specific changelog parameters, see here (split'statements etc) but that did not work either
  2. You can add a single xml based changelog (along with your sqls - liquibase has no problem at all) and then define all your trigger declarations using the tag. In one file and with a single chageset id - you can define all your triggers. This is what I did for now, it is pretty clean , structured and I can have all these evil triggers in one place.
  3. You can experiment with Oracle Specific liquibase extention , which has several changelog specific tabs for supporting oracle specifics and notation. This of course means you have to use the xml based format. I am going to experiment with this extention as soon as I have some more time.

Happy DB Schema maintainance .stories!