CODEWITHSUNDEEP
oracle-databaseplsqltriggersprocedure
Or Med
Or Med

Reputation: 127

Create trigger that deletes rows

So I have this 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 I want to create a trigger that when update changes quantity to be 0 (zero) -> delete this row.

I tried this:

CHECK_ORDER_ROW trigger:

create or replace
TRIGGER check_order_row
  AFTER INSERT OR UPDATE on orders_rows
  for each row
BEGIN
  if :new.quantity = 0 then
  DELETE_ORDER_ROW(:new.order_id, :new.row_num);
  end if;
END;

DELETE_ORDER_ROW is the procedure:

DELETE_ORDER_ROW procedure:

create or replace
PROCEDURE delete_order_row (p_order_id NUMBER, p_row_num NUMBER)
IS
BEGIN
  DELETE orders_rows WHERE (order_id = p_order_id and row_num = p_row_num);
  COMMIT;
END;

But when I try to update:

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

I get error:

A trigger (or a user defined plsql function that is referenced in
           this statement) attempted to look at (or modify) a table that was
           in the middle of being modified by the statement which fired it.

Any changes I can do? Or try other options to do that?

Thanks in advance!

Upvotes: 0

Views: 147

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

You probably can't (or at least you shouldn't).

A row-level trigger on a table cannot generally query that same table without causing a mutating table exception. If you were really determined, you could create a package, create a collection of order_id values in that package, create a before statement trigger that initializes the collection, create a row-level trigger that populated the collection with the :new.order_id, and then an after statement trigger that iterated through the collection and called delete_order_row. That, however, is a lot of moving pieces to deal with. It would generally make much more sense for the application code that is setting the quantity to 0 to delete the row instead. Putting logic into a trigger that deletes a row that an application just inserted (or updated) generally leads to application flows that are very difficult to follow (in part because you can never see the entire flow at once, you're constantly looking to see what side-effects triggers are creating) and bugs that are very difficult to understand and debug.

If you're determined to use a trigger and you don't want to go with the three-trigger solution, you could also rename the table, create a view named order_row and create an instead of trigger on the view that turned an update on the view into an update or a delete on the base table depending on the quantity value. That's adding an extra layer of indirection to your code, though, and it still tends to make application flows difficult to follow.

Upvotes: 2

Related Questions