Oracle 18 c trigger error sql ;error during execution of trigger

Question

The table is tied to itself A positive entry movement and a negative exit movement associate all of the positive movements with all of their negative movements With every new addition I want to Total all the negative movements of the positive movements

When I try to enter a new value into the table, an error appears in the trigger, and I do not know the reason even though there are no errors in the SQL statements error during execution of trigger 'AMMAR.ITEMS_MOVE_DETAILl_TRIGGER

create or replace trigger ITEMS_MOVE_DETAIL1_trigger
  after insert 
  on ITEMS_MOVE_DETAIL1 
  for each row
declare
 sumqu float;
begin
 
select sum(quantity) 

into sumqu
from ITEMS_MOVE_DETAIL1
 where self_id = :new.self_id;


update  ITEMS_MOVE_DETAIL1
  set drawn_quantity = sumqu
   where swid = :new.self_id;
  

end ITEMS_MOVE_DETAIL1_trigger;

Answer 1

Well, that won't work. You're selecting from the same table that is being updated which makes the table mutating. We lack some more info about what it really is (e.g. how many rows does the table contain for each SWID and SELF_ID; what is their relationship; should you really store that value into the table (or can you calculate it when needed)?

There is a way to "fix" mutating table error. Nowadays, it is a compound trigger that enables it. In prior Oracle database versions, you'd use a type + package solution (search the Internet for examples).

I'd, though, suggest you to think it over. Mutating table error usually (but not always) means that you're doing it wrong. If possible, change the process.