CODEWITHSUNDEEP
sqloracle-databasetriggersinsert
user2838928
user2838928

Reputation: 57

ORACLE SQL Trigger - no data found

I have created a trigger so that when a booking record is inserted or updated, if the evaluation is 0 then details of the booking are inserted into an audit table. The following trigger works perfectly if i update a record, but on insert it gives me the following error:

INSERT INTO "SCOTT"."BOOKING" (PASSENGER_PASSENGER_ID, VOYAGE_VOYAGE_ID, CABIN_NUM, CLASS, EVALUATION) VALUES ('2', '1', '202', 'SECOND', '0')
ORA-01403: no data found
ORA-01403: no data found
ORA-06512: at "SCOTT.EVALUATION_TRIG", line 8
ORA-04088: error during execution of trigger 'SCOTT.EVALUATION_TRIG'
ORA-06512: at line 1


One error saving changes to table "SCOTT"."BOOKING":
Row 2: ORA-01403: no data found
ORA-01403: no data found
ORA-06512: at "SCOTT.EVALUATION_TRIG", line 8
ORA-04088: error during execution of trigger 'SCOTT.EVALUATION_TRIG'
ORA-06512: at line 1

The Oracle SQL Trigger code:

create or replace trigger EVALUATION_TRIG
AFTER INSERT OR UPDATE
ON BOOKING 
FOR EACH ROW 
WHEN (NEW.EVALUATION =0)
DECLARE 
PRAGMA AUTONOMOUS_TRANSACTION;
PASSENGER_NAME VARCHAR2(30);
CRUISE_NAME VARCHAR2(30);
VOYAGE_DATE DATE;
SHIP_NAME1 VARCHAR2(30);
BEGIN 
SELECT NAME INTO PASSENGER_NAME FROM PASSENGER JOIN BOOKING
ON PASSENGER.PASSENGER_ID = BOOKING.PASSENGER_PASSENGER_ID
WHERE BOOKING.PASSENGER_PASSENGER_ID = :NEW.PASSENGER_PASSENGER_ID
AND ROWNUM  = 1;
SELECT NAME INTO CRUISE_NAME FROM CRUISE JOIN VOYAGE 
ON CRUISE.CRUISE_ID = VOYAGE.CRUISE_CRUISE_ID 
JOIN BOOKING ON
VOYAGE.VOYAGE_ID = BOOKING.VOYAGE_VOYAGE_ID
WHERE BOOKING.VOYAGE_VOYAGE_ID = :NEW.VOYAGE_VOYAGE_ID
AND ROWNUM= 1;
SELECT START_DATE INTO VOYAGE_DATE FROM VOYAGE JOIN BOOKING
ON VOYAGE.VOYAGE_ID= BOOKING.VOYAGE_VOYAGE_ID
WHERE BOOKING.VOYAGE_VOYAGE_ID = :NEW.VOYAGE_VOYAGE_ID
AND ROWNUM = 1;
SELECT SHIP_NAME INTO SHIP_NAME1 FROM SHIP JOIN VOYAGE 
ON SHIP.SHIP_ID = VOYAGE.SHIP_SHIP_ID JOIN BOOKING 
ON VOYAGE.VOYAGE_ID= BOOKING.VOYAGE_VOYAGE_ID
WHERE BOOKING.VOYAGE_VOYAGE_ID = :NEW.VOYAGE_VOYAGE_ID
AND ROWNUM = 1;
 INSERT INTO EVALUATION_AUDIT VALUES (PASSENGER_NAME, CRUISE_NAME,VOYAGE_DATE, SHIP_NAME1,:NEW.EVALUATION);
 COMMIT;
 END;

Upvotes: 2

Views: 9169

Answers (1)

GolezTrol
GolezTrol

Reputation: 116100

That error occurs when you execute a select..into statement that doesn't return a row, for instance the first one in your trigger.

SELECT NAME INTO PASSENGER_NAME FROM PASSENGER JOIN BOOKING
ON PASSENGER.PASSENGER_ID = BOOKING.PASSENGER_PASSENGER_ID
WHERE BOOKING.PASSENGER_PASSENGER_ID = :NEW.PASSENGER_PASSENGER_ID
AND ROWNUM  = 1;

Those :new values should be filled in for inserts just as well as updates, but that doesn't mean that the actual data exists as well.

The query above joins passenger on booking, but if you are inserting the first booking for that passenger, there is no current one. Besides, to get the name of the passenger, you don't need the booking at all. Moreover, querying on a table X in a row level trigger on that same table can give problems.

So, long story short, I think that statement above should look like this:

SELECT NAME INTO PASSENGER_NAME 
FROM PASSENGER 
WHERE PASSENGER.PASSENGER_ID = :NEW.PASSENGER_PASSENGER_ID;

The same change also needs to be made to the other statements.

After that, you could theoretically still get the same error, but only if :NEW.PASSENGER_PASSENGER_ID doesn't match the id of any passenger. But in that case, I think you should get a foreign key constraint error first, if you have those constraints configured.

I also see a commit in at the end of the trigger. This also doesn't make much sense. The trigger is part of a statement, and the statement itself should be atomic. Committing from the trigger, if this would work at all, would automatically commit each row in multi-row inserts. Suggested reading material.

Upvotes: 3

Related Questions