DB2 Trigger Error – Fix Unexpected Token ‘OR INSERT’ Error

db2trigger

I am getting an error while executing the following queries. As per the DB2 documentation, we can have both update and insert event in the same trigger, but when I am trying to do so I am getting an error.

CREATE TABLE NULL_LOOKUP (
    ID INT NOT NULL,
    TABLE_NAME VARCHAR(100) NOT NULL
);

CREATE TABLE PARENT (
    PARENT_ID INT NOT NULL,
    PARENT_NAME VARCHAR(100) NOT NULL
);

CREATE TABLE CHILD_ONE (
    CHILD_ONE_ID INT NOT NULL,
    CHILD_NAME VARCHAR(100) NOT NULL,
    PARENT_ID INT
);

--#SET TERMINATOR @
CREATE OR REPLACE TRIGGER RESET_NULL_TRIGGER 
BEFORE UPDATE OF PARENT_ID OR INSERT ON CHILD_ONE 
REFERENCING NEW AS N 
FOR EACH ROW WHEN (N.PARENT_ID IS NULL) 
    BEGIN ATOMIC
    SET N.PARENT_ID = (SELECT ID FROM NULL_LOOKUP WHERE TABLE_NAME = 'PARENT');
    END
@ 
--#SET TERMINATOR ;

Best Answer

You need to replace BEGIN ATOMIC with just BEGIN in the trigger body.

BEGIN ATOMIC indicates an inlined compound SQL statement, BEGIN -- a compiled compound statement. Documentation states:

If multiple events are specified, the triggered action must be a compound SQL (compiled) statement.

Related Question