ORA-01403: no data found

Carbonecz
Carbonecz used Ask the Experts™
on
When I run this:

INSERT INTO "rezervace" ("rez_id", "spo_id", "zak_id", "rez_datum", "rez_od", "rez_do") VALUES ('4', '3', '3', TO_DATE('21.02.10', 'DD.MM.RR'), '1800', '1900')

Trigger should check whether there is already similar in the table. When it ithrows no data found exception it should add it, however it does this:

One error saving changes to table "rezervace":
Row 4: ORA-01403, no data found
ORA-01403, no data found
ORA-01403, no data found
ORA-01403, no data found
ORA-01403, no data found
ORA-01403, no data found
ORA-01403, no data found
etc.

thank you for your help
create or replace
trigger "kontrola_prekryvani"
before insert or update on "rezervace"
for each row

declare
"cas_od_rezervace" integer;
"cas_do_rezervace" integer;

begin

select "rez_od", "rez_do" into "cas_od_rezervace", "cas_do_rezervace" from "rezervace"
where "rezervace"."rez_datum"=:new."rez_datum" and "rezervace"."spo_id"=:new."spo_id";

exception
  when NO_DATA_FOUND then
    insert into "rezervace" values (:new."rez_id", :new."spo_id", :new."zak_id", :new."rez_datum", :new."rez_od", :new."rez_do");

if ("cas_od_rezervace" > 0 or "cas_do_rezervace" > 0) 
and (((:new."rez_od" >= "cas_od_rezervace") and (:new."rez_od" < "cas_do_rezervace")) 
or ((:new."rez_do" > "cas_od_rezervace") and (:new."rez_do" <= "cas_do_rezervace"))
or ((:new."rez_od" <= "cas_od_rezervace") and (:new."rez_do" >= "cas_do_rezervace")))
then RAISE_APPLICATION_ERROR(-20002,'Vaše rezervace se prekrývá s již podanou rezervací!');
end if;

end "kontrola_prekryvani";

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Project Architect
Commented:
Hi Carbonecz,

This seems to a bit of an odd way to do this ... and I would not recommend it.  It is getting into a loop as even the INSERT in the TRIGGER will fire the trigger again.

If you want to guard against duplicates ... use a unique or primary key index.

If you must go down this path ... then you do not need to repeat the INSERT, just allow the INSERT that fired the trigger to complete.

Also, the placement of your EXCEPTION block is not right ... they should be at the end of the BEGIN ... END blocks.  You can have multiple BEGIN ... END blocks.  see below for example:

lwadwell
create or replace
trigger "kontrola_prekryvani"
before insert or update on "rezervace"
for each row

declare
"cas_od_rezervace" integer;
"cas_do_rezervace" integer;

begin

  begin -- inner begin
    select "rez_od", "rez_do" into "cas_od_rezervace", "cas_do_rezervace" from "rezervace"
    where "rezervace"."rez_datum"=:new."rez_datum" and "rezervace"."spo_id"=:new."spo_id";
  exception
    when NO_DATA_FOUND then
      return;
  end; -- end of inner begin

if ("cas_od_rezervace" > 0 or "cas_do_rezervace" > 0) 
and (((:new."rez_od" >= "cas_od_rezervace") and (:new."rez_od" < "cas_do_rezervace")) 
or ((:new."rez_do" > "cas_od_rezervace") and (:new."rez_do" <= "cas_do_rezervace"))
or ((:new."rez_od" <= "cas_od_rezervace") and (:new."rez_do" >= "cas_do_rezervace")))
then RAISE_APPLICATION_ERROR(-20002,'Va?e rezervace se prekr?v? s ji? podanou rezervac?!');
end if;

end "kontrola_prekryvani";

Open in new window

Author

Commented:
Thank you!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial