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

0
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

0

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.