Nested if statements for trigger

Question

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.

you can use old and new for insert. I cant find your insert atatement, where is it? — jose_bacoy
– jose_bacoy, Commented Apr 17, 2018 at 0:00
I think it is possible to solve this problem without using a trigger, just foreign key relationships. — Gordon Linoff
– Gordon Linoff, Commented Apr 17, 2018 at 1:51
"the sections for each seat number must be occupied by students of the same gender" >> sex segregation? — Littlefoot
– Littlefoot, Commented Apr 17, 2018 at 7:58

Connor McDonald · Accepted Answer · 2018-04-17 07:21:08Z

It is actually incredibly hard to use a trigger to implement that logic - it is really a modelling problem, ie, modelling the tables to allow standard database constraints to control the data validation.

In particular, without database constraints, there is the challenge is making sure that person X is not adding/updating rows, whilst person Y is doing the same, and creating violations with uncommitted changes (that your session cannot see).

So here is an example of how you could implement it, but this is a brute force approach, namely, after the insert has been allowed to occur we fire a trigger that will

lock the table, so we have exclusive access to it
look for data violations, not just for the rows modified in this transaction, but for all rows

but I stress, this generally not a sustainable solution in the 'real world'

create or replace trigger trigstudent
after insert or update on classroom 
DECLARE
  x int;
BEGIN
  lock table classroom in exclusive mode nowait;

  select count(*) into x from dual
  where exists ( 
    select count(*) from classroom group by seat# having count(*) > 1 
    );

  if x > 0 then
    raise_application_error(-20000,'There are some seat duplicates');
  end if;

  select count(*) into x from dual
  where exists ( 
    select count(distinct gender) from classroom group by section# having count(distinct gender) > 1
    );

  if x > 0 then
    raise_application_error(-20000,'There are some clashing genders per section');
  end if;
end;
/

Collectives™ on Stack Overflow

Nested if statements for trigger

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related