HELP! Problem with a trigger

Hi Nathan,

        I am using the latest 2.0 code, from the trunk and I have this situation where I have to use splitStatements=false on a trigger creation because otherwhise it doesn’t work:

Here is the example:

                            CREATE TABLE DATE_TABLE             (               DATE_ID        NUMBER(11,0) NOT NULL,               E_DATE        DATE NOT NULL,               HOLIDAY        VARCHAR2(1) DEFAULT 'N' NOT NULL ,               DESCRIPTION    VARCHAR2(100) NULL             );                         ALTER TABLE DATE_TABLE ADD CONSTRAINT DATE__TABLE_PK PRIMARY KEY (DATE_ID);                                            CREATE OR REPLACE TRIGGER UPDATE_HOLIDAYS               AFTER INSERT OR UPDATE OR DELETE ON HOLIDAYS               FOR EACH ROW             BEGIN                   IF INSERTING OR UPDATING THEN                     UPDATE DATE_TABLE SET HOLIDAY = 'Y', DESCRIPTION = :new.DESCRIPTION WHERE E_DATE = :new.DAY;                   ELSIF DELETING THEN                     UPDATE DATE_TABLE SET HOLIDAY = 'N', DESCRIPTION = NULL WHERE E_DATE = :old.DAY;                   END IF;             END;               

If I use it that way (2 different changesets) it works. But since I have only one sql file with everything, and I would like to reuse the sql like it is right now, I’m not able to make this work, no matter what option I use in the tag it gives me always some kind of error.

The sql file contains something similar to what is put here in this full example:

                        CREATE TABLE DATE_TABLE             (               DATE_ID        NUMBER(11,0) NOT NULL,               E_DATE        DATE NOT NULL,               HOLIDAY        VARCHAR2(1) DEFAULT 'N' NOT NULL ,               DESCRIPTION    VARCHAR2(100) NULL             );                         ALTER TABLE DATE_TABLE ADD CONSTRAINT DATE__TABLE_PK PRIMARY KEY (DATE_ID);                             CREATE OR REPLACE TRIGGER UPDATE_HOLIDAYS               AFTER INSERT OR UPDATE OR DELETE ON HOLIDAYS               FOR EACH ROW             BEGIN                   IF INSERTING OR UPDATING THEN                     UPDATE DATE_TABLE SET HOLIDAY = 'Y', DESCRIPTION = :new.DESCRIPTION WHERE E_DATE = :new.DAY;                   ELSIF DELETING THEN                     UPDATE DATE_TABLE SET HOLIDAY = 'N', DESCRIPTION = NULL WHERE E_DATE = :old.DAY;                   END IF;             END;            

Do you know what could I do in this case?

Should I split the file as the only possible solution?

Thanks a lot,
Alexis.

You should be able to use splitStatement=“true” endDelimiter=“GO” or some other end delimiter.  That way, it will not attempt to split statements on ;s and break your create trigger, but it will also allow you to have multiple statements. 

You would have to modify your SQL file to change your statement delimiters to GO (or whatever else you like).  The trouble is that JDBC seems to require statemetns to come in one at a time.

You could also try the formatted SQL: http://blog.liquibase.org/2010/05/liquibase-formatted-sql.html and add a --changeSet comment before each statement.  That way you can turn off splitStatements for your trigger statements ,but keep the default for the rest.  It would require less modification of your SQL file, and you also get better granualarity of which statement have actually ran. 

Nathan

For Oracle standard delimiter is “/”
It could ends every ddl/dml statement up and it would be understanding by oracle’s sql*plus utility or other one, dedicated to work with oracle.
Note that / should be at first position of the line and final semicolon of the statements should be removed. In the liquibase though, it could break the source if there is occurred in the text as part of string for example. To resolve this case I suggest (to Nathan) add additional argument to the changesSet tag (as it made in the sql-maven-plugin http://mojo.codehaus.org/sql-maven-plugin/execute-mojo.html#delimiterType). The delimiterType=“row” would prevent the parser accidentally splitting.

                        CREATE TABLE DATE_TABLE             (               DATE_ID        NUMBER(11,0) NOT NULL,               E_DATE        DATE NOT NULL,               HOLIDAY        VARCHAR2(1) DEFAULT 'N' NOT NULL ,               DESCRIPTION    VARCHAR2(100) NULL             ) /                         ALTER TABLE DATE_TABLE ADD CONSTRAINT DATE__TABLE_PK PRIMARY KEY (DATE_ID) /              

                CREATE OR REPLACE TRIGGER UPDATE_HOLIDAYS
                  AFTER INSERT OR UPDATE OR DELETE ON HOLIDAYS
                  FOR EACH ROW
                BEGIN
                      IF INSERTING OR UPDATING THEN
                        UPDATE DATE_TABLE SET HOLIDAY = ‘Y’, DESCRIPTION = :new.DESCRIPTION WHERE E_DATE = :new.DAY;
                      ELSIF DELETING THEN
                        UPDATE DATE_TABLE SET HOLIDAY = ‘N’, DESCRIPTION = NULL WHERE E_DATE = :old.DAY;
                      END IF;
                END
    /
           
       

Thanks Guys, now I could fix it… The thing was this: the / existed at the end of the file only and the other SQL statements used semicolon so that’s why it failed. I have something similar in other files with simpler triggers and they worked well, but it seems that for this specific trigger it failed.

What I did was use / as the ending character and put the options suggested.

Nathan,

    I’m not using that because what I’m doing is generate all the xml with the rollbacks for all the exisiting sql files I have. And I don’t know how to write the rollback with the formatted SQL option.

Thanks again,
Alexis.

If you do ever want to use the formatted sql, you list the rollback sql with  --rollback YOUR SQL HERE lines. 

I mainly mentioned it to help get the word out about the non-xml format :slight_smile:

Nathan