How to create a trigger before insert in db2

989 views Asked by At

I have three tables below:

enter image description here

I need to create a trigger to disallow students to take a class without completing their pre-requisites (must use a trigger). The trigger must return an error message "Missing Pre-req" when trying to insert a class without its proper pre-req.

So far, I wrote a command below:

create trigger checkPrereq 
before insert on HW3.SCHEDULE
referencing new as newRow
for each row 
When 
(
  select GRADE 
  from HW3.SCHEDULE
  where HW3.SCHEDULE.ClassId = 
  (
    select PrereqId from HW3.CLASS_PREREQ
    where newRow.ClassId = HW3.CLASS_PREREQ.ClassId
  )
) in ('F', null) 
begin atomic
  set newRow.Semester = null; 
  signal sqlstate 'KILLED' set message_text = ('Missing Pre-req');
end

but I got a warning:

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 "GRADE" was found following "ach row When (select". Expected tokens may include: ")". LINE NUMBER=1. SQLSTATE=42601

I can not understand what was happening here. Could you please help fix this? Any help is appreciated!

0

There are 0 answers