1

What is wrong in the below trigger syntax, I am getting this error An unexpected token "INT" was found following " DECLARE NULL_ROW_ID". Expected tokens may include: "END-OF-STATEMENT".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.68.61

CREATE TABLE A (
    A_ID INT NOT NUll,
    A_CODE VARCHAR(100) NOT NULL,
    PRIMARY KEY(A_ID)
);

CREATE TABLE B (
    B_ID INT NOT NUll,
    B_CODE VARCHAR(100) NOT NULL,
    A_ID INT,
    PRIMARY KEY (B_ID),
    FOREIGN KEY (A_ID) REFERENCES A (A_ID) 
);

CREATE OR REPLACE TRIGGER B_TRIGGER
AFTER UPDATE OF A_ID ON B
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
    WHEN (N.A_ID IS NULL)
    BEGIN ATOMIC
        DECLARE NULL_ROW_ID INT;
        SET NULL_ROW_ID = (SELECT A_ID FROM A WHERE A_CODE = 'Custom Null String');
        IF NULL_ROW_ID IS NULL THEN
            SIGNAL SQLSTATE '70000' SET MESSAGE_TEXT = 'Custom Null String row not found.';
        END IF;
    SET N.A_ID = NULL_ROW_ID;
    END
@

It's on DB2 Express-C 10.5

  • You have FOR EACH ROW twice :...REFERENCING NEW AS N OLD AS O FOR EACH ROW FOR EACH ROW WHEN ... – a1ex07 Jun 26 '15 at 16:23
  • Oh my bad, but the problem still exists even after removing "For EACH ROW". – johncorner06 Jun 26 '15 at 16:28
  • I'm almost sure it is a statement terminator problem. You may want to indicate how you run these SQL statements, with different terminators, in your script. – mustaccio Jun 26 '15 at 16:46
  • How can I indicate that? Can I do it within script? – johncorner06 Jun 26 '15 at 17:08
  • I mean, indicate to us here how you run your script, because the way to specify the statement terminator depends on that. – mustaccio Jun 26 '15 at 20:04
1

I agree with what others have said, it is most likely a problem with the statement terminator. Try adding:

--#SET TERMINATOR @
CREATE OR REPLACE TRIGGER B_TRIGGER
[...]
@
--#SET TERMINATOR ;

You might also consider using for example @ as your statement terminator globally. Another "undocumented" feature is to quote the ; in the trigger using to ending dashes. Example:

CREATE OR REPLACE TRIGGER B_TRIGGER
AFTER UPDATE OF A_ID ON B
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
    WHEN (N.A_ID IS NULL)
    BEGIN ATOMIC
        DECLARE NULL_ROW_ID INT; --
        SET NULL_ROW_ID = (SELECT A_ID FROM A WHERE A_CODE = 'Custom Null String'); --
        IF NULL_ROW_ID IS NULL THEN
            SIGNAL SQLSTATE '70000' SET MESSAGE_TEXT = 'Custom Null String row not found.'; --
        END IF; --
        SET N.A_ID = NULL_ROW_ID; --
    END ;

Personally I prefer to use - say - @ globally and change the environment to adjust for that

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.