A new topic, 'HELP! Problem with a trigger', has been made on a board you are
watching.
You can see it at
http://liquibase.org/forum/index.php?topic=639.new#new
The text of the topic is shown below:
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:
Code:
<changeSet id="test-date-table" author="alexis" >
<sql>
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);
</sql>
</changeSet>
<changeSet id="test-date-trigger" author="alexis" >
<sql splitStatements="false">
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;
</sql>
</changeSet>
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
<sql> tag it gives me always some kind of error.
The sql file contains something similar to what is put here in this full
example:
Code:
<changeSet id="test-date-wrong-trigger" author="alexis" >
<sql>
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;
</sql>
</changeSet>
Do you know what could I do in this case?
Should I split the file as the only possible solution?
Thanks a lot,
Alexis.
Unsubscribe to new topics from this board by clicking here:
http://liquibase.org/forum/index.php?action=notifyboard;board=1.0
Regards,
The Liquibase Community Forum Team.------------------------------------------------------------------------------
This SF.net email is sponsored by
Make an app they can't live without
Enter the BlackBerry Developer Challenge
http://p.sf.net/sfu/RIM-dev2dev
_______________________________________________
Liquibase-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/liquibase-user
