DB2 – Syntax Error While Creating Trigger

db2

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

Best Answer

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

Related Question