Create Trigger with select of non existence

Question

I'm looking forward to create a trigger on sql oracle and my goal is to check if there is no other room being occupied at the same time by another "class" in the same table.

So, I thought I could create something like this:

    create trigger checkRoomBusy
    before insert on tiime
    for each row
    begin


    IF (not exists(select count(*) from tiime where room=:new.room AND daay=:new.daay AND hoour=:new.hoour AND miin=:new.miin))
       THEN
          insert into tiime (daay, hoour, miin, class_id)
values(:new.daay, :new.hoour, :new.miin, :new.class_id);
    END IF;

    END;
    /

but my sgbd gave me the error:

ORA-24344: success with compilation error

what can I improve to avoid the error and make the trigger workable?

PS: I can just use the 11g sql oracle.

Thank you in advance.

Two things: count always exists - so your IF condition seems to be always false. Secondly, your trigger is subject to Mutating Table error — micklesh, May 19 '16 at 20:02
what do u mean with my trigger being subject to mutating table error? — , May 19 '16 at 20:20
here's a nice answer to that: http://dba.stackexchange.com/a/5439 — micklesh, May 19 '16 at 20:30
Do you possibly just want a unique constraint on `(room, daay, hoour, miin)` (not sure why you're adding extra vowels to your column names, that seems likely to cause confusion in the future). As it stands, there are a host of issues with your trigger. A row-level trigger on a table cannot query that table. `if not exists` is not a valid PL/SQL construct. A `count(*)` will always exist even if it is 0. You wouldn't insert a row into `tiime` in a trigger on `tiime`. You could potentially throw an exception to prevent the insert from happening but I'm not sure that's what you want. — Justin Cave, May 19 '16 at 21:43

Create Trigger with select of non existence

0 Answers0