How to fix trigger with check compiler error?

Question

Please, help!

I have following trigger:

CREATE TRIGGER check_rezervace 
  BEFORE INSERT ON rezervuje 
  FOR EACH ROW 
DECLARE 
    chyba INTEGER; 
BEGIN 
    SELECT Count(*) 
    INTO   chyba 
    FROM   rezervuje 
           join (SELECT rezervace_od 
                 INTO   new_od 
                 FROM   objednavka 
                 WHERE  id_objednavky = :new.objednavka_id_objednavky) 
             ON objednavka.id_objednavky = rezervuje.objednavka_id_objednavky 
           join (SELECT rezervace_do 
                 INTO   new_do 
                 FROM   objednavka 
                 WHERE  id_objednavky = :new.objednavka_id_objednavky) 
             ON objednavka.id_objednavky = rezervuje.objednavka_id_objednavky 
    WHERE  rezervuje.pokoj_cislo_pokoje = :new.pokoj_cislo_pokoje 
           AND ( objednavka.rezervace_od < :new.new_od 
                 AND :new.new_od < objednavka.rezervace_do ) 
            OR ( objednavka.rezervace_od < :new.new_od 
                 AND :new.new_do < objednavka.rezervace_do ) 
            OR ( :new.new_od <= objednavka.rezervace_od 
                 AND objednavka.rezervace_do <= :new.new_do ); 

    IF chyba > 0 THEN 
      Raise_application_error(-20001, 'rezervace již existuje'); 
    END IF; 
END;

I have check compiler mistake:

Trigger CHECK_REZERVACE compiled

LINE/COL  ERROR
--------- -------------------------------------------------------------
3/3       PL/SQL: SQL Statement ignored
4/24      PL/SQL: ORA-01744: ???????????? INTO
9/35      PLS-00049: ???????? ?????????? ???????? 'NEW.NEW_OD'
9/51      PLS-00049: ???????? ?????????? ???????? 'NEW.NEW_OD'
10/35     PLS-00049: ???????? ?????????? ???????? 'NEW.NEW_OD'
10/51     PLS-00049: ???????? ?????????? ???????? 'NEW.NEW_DO'
11/9      PLS-00049: ???????? ?????????? ???????? 'NEW.NEW_OD'
11/79     PLS-00049: ???????? ?????????? ???????? 'NEW.NEW_DO'
Errors: check compiler log

How to fix it? This trigger doesn't allow to add reservation(reservace_od/reservace_do) to the room if the reservation already existed fro this date.

Please **edit your question** using the `edit` button just below the tags and include the error which is being produced. Thanks. — Bob Jarvis - Слава Україні, May 07 '20 at 15:44
Does this answer your question? [SQL: trigger to prevent invalid data from being inserted into a table](https://stackoverflow.com/questions/58520408/sql-trigger-to-prevent-invalid-data-from-being-inserted-into-a-table) — Ankit Bajpai, May 07 '20 at 15:54

score 0 · Answer 1 · answered May 07 '20 at 16:01

There are plenty of errors in your code. I have tried to fix those errors. You may try below code -

CREATE TRIGGER check_rezervace 
  AFTER INSERT ON rezervuje 
DECLARE 
    chyba INTEGER; 
BEGIN 
    SELECT Count(*) 
    INTO   chyba 
    FROM   rezervuje 
    join objednavka ON objednavka.id_objednavky = rezervuje.objednavka_id_objednavky 
    WHERE ( objednavka.rezervace_od < rezervace_od
                 AND rezervace_od < objednavka.rezervace_do ) 
            OR ( objednavka.rezervace_od < rezervace_od
                 AND rezervace_do < objednavka.rezervace_do ) 
            OR ( rezervace_od <= objednavka.rezervace_od 
                 AND objednavka.rezervace_do <= rezervace_do ); 

    IF chyba > 0 THEN 
      Raise_application_error(-20001, 'rezervace již existuje'); 
    END IF; 
END;

Initially you have INTO clause in you JOIN statements which was not correct, Also you cannot run select on the same table which is getting updated. it will thorough mutating table error. So I have converted your trigger to after statement trigger.

I am not sure this trigger will work or not since we do not have the sample data in tables rezervuje and objednavka tables. Please provide sample data in these tables to get better help.

Thank you for your answer! Sample of data from objednavka: INSERT INTO Objednavka (id_objednavky,castka,rezervace_od,rezervace_do,hotel_id_hotelu,klient_cislo_pasu) VALUES('000005', '2700', '20.12.2020', '02.01.2021', '003', '3338888'); — nikita1221, May 07 '20 at 16:08

How to fix trigger with check compiler error?

1 Answers1