0

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 ;
  • What DB2 version? Make sure you reference documentation for the actual version you're using. – mustaccio Jul 7 '15 at 18:38
  • It's on DB2 Express-C 10.5 – johncorner06 Jul 7 '15 at 18:41
  • Try BEGIN in place of BEGIN ATOMIC. – mustaccio Jul 7 '15 at 18:51
  • Yes, that worked. Thank you very much. How come BEGIN works but not BEGIN ATOMIC ? – johncorner06 Jul 7 '15 at 19:05
1

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.

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.