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.