Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

DB2 inserting of triggers fails, depending on the format of the statement #1634

Closed
sandman24 opened this issue May 12, 2017 · 0 comments
Closed

Comments

@sandman24
Copy link

@sandman24 sandman24 commented May 12, 2017

What version of Flyway are you using?

4.2.0

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin, SBT plugin, ANT tasks)

Command-Line

What database are you using (type & version)?

DB2 9.7

What operating system are you using?

Windows

What did you do?

(Please include the content causing the issue, any relevant configuration settings, and the command you ran)
Trying to setup a trigger in DB2 fails depending on the statements formatting.
If BEGIN is found at the beginning of any row in the statement the import fails.

What's that special with BEGIN??
(Think the problem is not restricted to DB2 merley)

--#SET TERMINATOR /

CREATE OR REPLACE TRIGGER I_TRIGGER_I
BEFORE INSERT ON I_TRIGGER
REFERENCING NEW AS NEW FOR EACH ROW
BEGIN ATOMIC
IF NEW.ID IS NULL
THEN SET NEW.ID = NEXTVAL FOR SEQ_I_TRIGGER;
END IF;
END
/

-- Note and workaround
if statement is re-formatted as below it works (but took me an hour to elaborate)
CREATE OR REPLACE TRIGGER I_TRIGGER_I
BEFORE INSERT ON I_TRIGGER
REFERENCING NEW AS NEW FOR EACH ROW BEGIN ATOMIC
IF NEW.ID IS NULL
THEN SET NEW.ID = NEXTVAL FOR SEQ_I_TRIGGER;
END IF;
END
/

What did you expect to see?

success

What did you see instead?

SQL State : 42601
Error Code : -104
Message : BEGIN ATOMIC
IF ;S NEW FOR EACH ROW
;<psm_begin_function_compou

@axelfontaine axelfontaine added this to the Flyway 5.0.0 milestone May 15, 2017
axelfontaine added a commit to flyway/flywaydb.org that referenced this issue Jul 11, 2017
dohrayme pushed a commit to dohrayme/flyway that referenced this issue Feb 3, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants
You can’t perform that action at this time.