Oracle PL/SQL: Calling a procedure from a trigger

Question

I get this error when ever I try to fire a trigger after insert on passengers table. this trigger is supposed to call a procedure that takes two parameters of the newly inserted values and based on that it updates another table which is the booking table. however, i am getting this error:

ORA-04091: table AIRLINESYSTEM.PASSENGER is mutating, trigger/function may not see it 
 ORA-06512: at "AIRLINESYSTEM.CALCULATE_FLIGHT_PRICE", line 11 ORA-06512: at 
"AIRLINESYSTEM.CALCULATE_FLIGHT_PRICE", line 15 ORA-06512: at 
 "AIRLINESYSTEM.CALCULATE_FLIGHT_PRICE_T1", line 3 ORA-04088: error during execution of 
  trigger 'AIRLINESYSTEM.CALCULATE_FLIGHT_PRICE_T1' (Row 3)

I complied and tested the procedure in the SQL command line and it works fine. The problem seems to be with the trigger. This is the trigger code:

create or replace trigger "CALCULATE_FLIGHT_PRICE_T1"
AFTER
insert on "PASSENGER"
for each row

begin

CALCULATE_FLIGHT_PRICE(:NEW.BOOKING_ID);

end;​​​​​

Why is the trigger isn't calling the procedure?

Answer 1

You are using database triggers in a way they are not supposed to be used. The database trigger tries to read the table it is currently modifying. If Oracle would allow you to do so, you'd be performing dirty reads. Fortunately, Oracle warns you for your behaviour, and you can modify your design.

The best solution would be to create an API. A procedure, preferably in a package, that allows you to insert passengers in exactly the way you would like it. In pseudo-PL/SQL-code:

procedure insert_passenger
( p_passenger_nr   in number
, p_passenger_name in varchar2
, ...
, p_booking_id     in number
, p_dob            in number
)
is
begin
  insert into passenger (...)
  values
  ( p_passenger_nr
  , p_passenger_name
  , ...
  , p_booking_id
  , p_dob
  );
  calculate_flight_price
  ( p_booking_id
  , p_dob
  );
end insert_passenger;
/

Instead of your insert statement, you would now call this procedure. And your mutating table problem will disappear.

If you insist on using a database trigger, then you would need to avoid the select statement in cursor c_passengers. This doesn't make any sense: you have just inserted a row into table passengers and know all the column values. Then you call calculate_flight_price to retrieve the column DOB, which you already know. Just add a parameter P_DOB to your calculate_flight_price procedure and call it with :new.dob, like this:

create or replace trigger calculate_flight_price_t1
after insert on passenger
for each row
begin
  calculate_flight_price
  ( :new.booking_id
  , :new.dob
  );  
end;

Answer 2

Oh my goodness... You are trying a Dirty Read in the cursor. This is a bad design. If you allow a dirty read, it return the wrong answer, but also it returns an answer that never existed in the table. In a multiuser database, a dirty read can be a dangerous feature.

The point here is that dirty read is not a feature; rather, it's a liability. In Oracle Database, it's just not needed. You get all of the advantages of a dirty read—no blocking—without any of the incorrect results.

Read more on "READ UNCOMMITTED isolation level" which allows dirty reads. It provides a standards-based definition that allows for nonblocking reads.

Other way round

You are misusing the trigger. I mean wrong trigger used.

you insert / update a row in table A and a trigger on table A (for each row) executes a query on table A (through a procedure)??!!!

Oracle throws an ORA-04091 which is an expected and normal behavior, Oracle wants to protect you from yourself since it guarantees that each statement is atomic (i.e will either fail or succeed completely) and also that each statement sees a consistent view of the data

You would expect the query (2) not to see the row inserted on (1). This would be in contradiction

Solution: -- use before instead of after

CREATE OR REPLACE TRIGGER SOMENAME
BEFORE INSERT OR UPDATE ON SOMETABLE