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

1 Answer 1

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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.