oracle - Create trigger that deletes rows -

so have table:

create table orders_rows (   order_id       number(10)   primary key,   row_num        date  not null,   p_id           number(10)  not null,   quantity       number(10)  not null, ); 

and want create trigger when update changes quantity 0 (zero) -> delete row.

i tried this:

check_order_row trigger:

create or replace trigger check_order_row   after insert or update on orders_rows   each row begin   if :new.quantity = 0   delete_order_row(:new.order_id, :new.row_num);   end if; end; 

delete_order_row procedure:

delete_order_row procedure:

create or replace procedure delete_order_row (p_order_id number, p_row_num number) begin   delete orders_rows (order_id = p_order_id , row_num = p_row_num);   commit; end; 

but when try update:

update orders_rows set quantity=0 (order_id=1 , row_num=1); 

i error:

a trigger (or user defined plsql function referenced in            statement) attempted @ (or modify) table            in middle of being modified statement fired it. 

any changes can do? or try other options that?

thanks in advance!

you can't (or @ least shouldn't).

a row-level trigger on table cannot query same table without causing mutating table exception. if determined, create package, create collection of order_id values in package, create before statement trigger initializes collection, create row-level trigger populated collection :new.order_id, , after statement trigger iterated through collection , called delete_order_row. that, however, lot of moving pieces deal with. make more sense application code setting quantity 0 delete row instead. putting logic trigger deletes row application inserted (or updated) leads application flows difficult follow (in part because can never see entire flow @ once, you're looking see side-effects triggers creating) , bugs difficult understand , debug.

if you're determined use trigger , don't want go three-trigger solution, rename table, create view named order_row , create instead of trigger on view turned update on view update or delete on base table depending on quantity value. that's adding layer of indirection code, though, , still tends make application flows difficult follow.