Facing issue while creating a simple DB2 trigger

Question

Tried to create following trriger:

CREATE TRIGGER EWR.INS_STU
AFTER INSERT ON EWR.STUDENT
    FOR EACH ROW
BEGIN
   IF ( :NEW.ROLL_NO > 60 ) THEN
      INSERT INTO EWR.STUDENT_DIV VALUES ( :NEW.ROLL_NO,'P');
   END IF;

   IF( :NEW.ROLL_NO < 60)  
   THEN
     INSERT INTO EWR.STUDENT_DIV VALUES (:NEW.ROLL_NO,'F');
   END IF;
END
!

But it is giving the following 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 ":NEW.ROLL_NO > 30 ) THEN INSERT INT" was found following "H ROW BEGIN IF (". Expected tokens may include:
"". LINE NUMBER=6. SQLSTATE=42601

SQL0104N An unexpected token ":NEW.ROLL_NO > 30 ) THEN INSERT INT" was found following "H ROW BEGIN IF (". Expected tokens may include: "".

Answer 1

Why are you using NEW with the notation as a host variable (:)? You do not need to put the colon before the variable name, because this is a trigger that uses only sql (sql pl).

Also, you have to declare how you are going to reference the new values, defined in the header.

REFERENCING NEW AS N

I recreated your case, and it works for me like this:

db2 "create table ewr.student(roll_no int)"
db2 "create table ewr.student_div(roll_no int, other char(1))"

trigger.sql

CREATE TRIGGER EWR.INS_STU
  AFTER INSERT ON EWR.STUDENT
  REFERENCING NEW AS NEW
  FOR EACH ROW
BEGIN
  IF ( NEW.ROLL_NO > 60 ) THEN
    INSERT INTO EWR.STUDENT_DIV VALUES ( NEW.ROLL_NO, 'P' );
  END IF;
  IF ( NEW.ROLL_NO < 60 ) THEN
    INSERT INTO EWR.STUDENT_DIV VALUES ( NEW.ROLL_NO, 'F' );
  END IF;
END !

db2 -td! -f trigger.sql
DB20000I  The SQL command completed successfully.

I hope this solve your problem.