PL/SQL ORACLE: Trigger updates on delete

Question

I am trying to make a trigger that increases the booked value and decreases the available value whenever new record is inserted inside the table ticket_price. If a record is deleted, I want it to decrease the booked value and increase the available value.

Although I am able to successfully make the trigger work for INSERT, I am unable to do the same for updating the values on deletion of a record.T his is the error I get whenever I try to delete a record

ORA-01403: no data found
ORA-06512: at "K.CAL", line 6
ORA-04088: error during execution of trigger 'K.CAL'

Just to clarify, I am updating values in another table, not the same table I am deleting!

Here is my code for the trigger:

CREATE OR REPLACE TRIGGER cal 
   BEFORE INSERT OR DELETE ON TICKET_PRICE FOR EACH ROW
DECLARE
    V_TICKET TICKET_PRICE.TICKETPRICE%TYPE;
    V_BOOKED FLIGHTSEAT.BOOKED_SEATS%TYPE;
    V_AVAILABLE FLIGHTSEAT.AVAILABLE_SEATS%TYPE;
BEGIN
    SELECT BOOKED_SEATS,AVAILABLE_SEATS 
    INTO V_BOOKED,V_AVAILABLE 
    FROM FLIGHTSEAT 
    WHERE SEAT_ID=:NEW.SEAT_ID;
    IF INSERTING THEN 
        V_BOOKED:=V_BOOKED+1;
        V_AVAILABLE:=V_AVAILABLE-1;
        UPDATE FLIGHTSEAT 
        SET BOOKED_SEATS=V_BOOKED, AVAILABLE_SEATS=V_AVAILABLE
        WHERE SEAT_ID=:NEW.SEAT_ID;
    ELSIF DELETING THEN 
        V_BOOKED:=V_BOOKED-1;
        V_AVAILABLE:=V_AVAILABLE+1;
        UPDATE FLIGHTSEAT 
        SET BOOKED_SEATS=V_BOOKED, AVAILABLE_SEATS=V_AVAILABLE 
        WHERE SEAT_ID=1;
    END IF;
END;

user_7623856 · Answer

You have correctly surmised that :new.seat is not available on the update for a delete. But neither is it available for the select and ow could you know sea_id=1 was need to be updated? For reference to Deleted row data use :Old.column name; is this case use :old_seat_id for both select and update.

But you don't need the select at all. Note: Further you have an implied assumption that seat_id is unique. I'll accept that below.

create or replace trigger cal 
    before insert or delete on ticket_price 
    for each row
declare
v_seat_id         flightseat.seat_id%type;          
v_booked          flightseat.booked_seats%type;     

begin
    if INSERTING then 
        v_booked  := 1;
        v_seat_id := :new.seat_id; 
    else
        v_booked  := -1;
        v_seat_id := :old.seat_id; 
    end if;

    update flightseat 
       set booked_seats=booked_seats+v_booked
         , available_seats=available_seats-v_booked 
     where seat_id = v_seat_id;

end;

PL/SQL ORACLE: Trigger updates on delete

1 Answers