Triggers in SQL syntax issue in code SQL DB2

Question

Question:

My code works until it reachs last line then it throws a syntax error.

Error :

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "temp_dept" was found following "(case when ". Expected tokens may include: "JOIN". SQLSTATE=42601

I am trying to do this:

After each insert on RD_EMP
for each row
insert into RD_Supervisor
check for cases, if temp_dept.RD_E_ID <= 0 THEN RD_Supervisor.RD_E_SUP 0

Code:

create trigger RD_total_dep_emp \
after insert on RD_Emp \
referencing new table as temp_dept
for each statement \
   insert into RD_Supervisor(RD_E_SUP, RD_E_EMP, RD_QUOT) \
   select temp_dept.RD_E_ID, 
       (case \
         when temp_dept.RD_E_ID <= 0 then 0 \
         when temp_dept.RD_E_ID > 0 AND temp_dept.RD_E_ID <= 15 then 15 \
         when temp_dept.RD_E_ID > 15 AND temp_dept.RD_E_ID <= 25 then 25 \
         when temp_dept.RD_E_ID > 25 AND temp_dept.RD_E_ID <= 35 then 35 \
         when temp_dept.RD_E_ID > 35 then 100 \ 
        end) as RD_E_SUP \ 
   from temp_dept

Gordon Linoff Gordon Linoff · Accepted Answer · 2015-04-09T23:22:44

You have three columns in the insert, but only two in the select -- and they appear to be in the wrong order. The following is probably more your intention:

create trigger RD_total_dep_emp \
after insert on RD_Emp \
referencing new table as temp_dept
for each statement \
   insert into RD_Supervisor(RD_E_EMP, RD_E_SUP) \
       select temp_dept.RD_E_ID, 
           (case \
             when temp_dept.RD_E_ID <= 0 then 0 \
             when temp_dept.RD_E_ID > 0 AND temp_dept.RD_E_ID <= 15 then 15 \
             when temp_dept.RD_E_ID > 15 AND temp_dept.RD_E_ID <= 25 then 25 \
             when temp_dept.RD_E_ID > 25 AND temp_dept.RD_E_ID <= 35 then 35 \
             when temp_dept.RD_E_ID > 35 then 100 \ 
            end) as RD_E_SUP \ 
       from temp_dept

If there is a value you want to set for RD_QUOT, then you can specify that as well -- both in the insert and the select.

Triggers in SQL syntax issue in code SQL DB2

2 Answers