1

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!

Mark Barinstein
  • 11,456
  • 2
  • 8
  • 16
David
  • 33
  • 4
  • 1
    You **must** use different from the default one (`;`) statement terminator character, if you want to run compound SQL statement. Place `@`, for example, at the end of your statement in a file `mysqlfile.sql` with your statement, and use the following command to run your statement: `db2 -td@ -f mysqlfile.sql`. If you use some another tool to run your statements, find the corresponding place there to change the statement terminator character. – Mark Barinstein Nov 07 '20 at 07:32
  • Secondly, look at the syntax of the [CREATE TRIGGER](https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000931.html) statement. Your syntax is not correct - `WHEN (search-condition)` can't be followed by `IN` or whatever expression. `search-condition` "Specifies a condition that is true, false, or unknown". – Mark Barinstein Nov 07 '20 at 20:44

0 Answers0