lundi 16 avril 2018

Nested if statements for trigger

So I am supposed to work with a table called classroom that includes studentid, gender(M/F), seat#, and seatsection(A/B). I am trying to create a trigger that prevents a user from inserting a student to an already existing seat# and seatsection. In addition, the sections for each seat number must be occupied by students of the same gender. If an insert statement violates these rules then appropriate error messages should be generated.

This is the rough draft that I came up with:

create or replace trigger trigstudent
before insert on classroom for each row
BEGIN
   if :new.seat# = :old.seat# then
      if :new.seatsection = :old.seatsection then
      -- raise an error message saying students can't take someone else's seat
      else
         if :new.gender = :old.gender then
            -- raise error message saying that only students of the same gender can sit together
         end if;
       end if;
    end if;

I obviously later realized that you can't use "new" and "old" for an insert statement. So I have just been stuck at this and not sure what to do. Any help will be appreciated.

Aucun commentaire:

Enregistrer un commentaire