CODEWITHSUNDEEP
oracle-databaseplsqltriggers
Azhar Nooh
Azhar Nooh

Reputation: 11

Error during execution of trigger

I'm making a flight booking system and trying to validate plane availability before assign it to a flight, I'm using Apex and doing this using a trigger. The trigger is working in insert case however in update case i got an error.

Error: 

    •ORA-04091: table FLIGHT is mutating, trigger/function may not see it ORA-06512: at
 "VALIDATEPLANE", line 2 ORA-04088: error during execution of trigger 'VALIDATEPLANE'

The Code: 

create or replace trigger "VALIDATEPLANE"
before
insert or update on "FLIGHT"
for each row
begin
    for index1 in(select * from FLIGHT)
    loop
        if :new.PLANE_NO = index1.PLANE_NO 
        then
            if :new.DEPARTUAL_DATE <index1.DEPARTUAL_DATE 
               or :new.DEPARTUAL_DATE > index1.DEPARTUAL_DATE
            then 
                if :new.ARRIVE_DATE<=index1.ARRIVE_DATE
                then
                    :new.ARRIVE_DATE := (index1.ARRIVE_DATE+1) + (:new.ARRIVE_DATE -:new.DEPARTUAL_DATE);
                    :new.DEPARTUAL_DATE := index1.ARRIVE_DATE +1;
                end if;
            ELSIF :new.DEPARTUAL_DATE =index1.DEPARTUAL_DATE
            then 
                :new.ARRIVE_DATE := (index1.ARRIVE_DATE+1) + (:new.ARRIVE_DATE -:new.DEPARTUAL_DATE);
                :new.DEPARTUAL_DATE := index1.ARRIVE_DATE +1;
            end if;
        end if;
     end loop;
end;

Upvotes: 0

Views: 1646

Answers (3)

San
San

Reputation: 4538

You cannot perform DML operations on the table for which trigger has fired. In this case you have created the trigger on table FLIGHT and fetching data from the same table i.e. FLIGHT.

Insert operation is working because it is a before trigger and there in no logic of firing the trigger on the row that has not yet created in the table. If it was a after trigger, you would have faced same error during insert operation as well.

One solution could be to introduce a parallel table that holds records similar to FLIGHT table and you can select data from that table for comparison in the trigger. You can insert data into that table well inside this trigger only. But you should avoid such solutions.

Upvotes: 0

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

Triggers are not a good tool here. The triggering statement could insert multiple rows, it could use multiple parallel threads to insert. The database thus can't guarantee to return a consistent set of rows during the insert: the database could literally insert several rows at the same time. To protect you, the database returns a mutating error.

Plus here for each inserted row, you're querying the whole table row by row (perfect example of Schlemiel the painter's algorithm).

In general, it is a bad idea to put complex business rules in triggers because:

  • the resulting code is fragmented (the business rule is separated from the actual insert)
  • thus it modifies the standard behaviour of DML in a hidden way
  • the logic of multiple triggers interacting with each other is hard to predict and creates hard to diagnose bugs
  • you can't use set logic in row triggers, so most of the time the code is sub-optimal.

If you run into a mutating error and insist on using triggers, here's an example of the kind of hoops you'll have to jump through: Avoiding mutating errors by Tom Kyte.

I advise that you use PL/SQL procedures instead. They are easier to code, read and maintain. They can be logically grouped in a nicely self-contained package. You will use set logic more easily, the gain in performance from row-by-row logic can be critical.

Upvotes: 3

Srini V
Srini V

Reputation: 11355

When you fire a insert statement, for eg INSERT INTO TABLE VALUES () it is very sure that you are trying to insert one single row.

But when you UPDATE or MERGE, the BEFORE INSERT trigger knows it is firing only for the first row. But in reality, It cannot obtain a stable set of rows. Try to rework on the set of rows affected.

Upvotes: 0

Related Questions