How to delete a row using trigger in oraclesql

Question

I am using 2 tables, orders and order_items. I am trying to create a trigger that when I delete an order_id from orders table a row with the same order_id will be deleted in order_items table.

CREATE OR REPLACE TRIGGER orders_order_items
AFTER DELETE ON orders
BEGIN
    DELETE FROM order_items WHERE order_items.order_id = orders.order_id;
END;

Could someone explain to me, why the above code does not work and how should it be written?

@jarlh I thought of that as well and it does not make sense to me why should I use a trigger, but this is for homework where I am required to do so — tuto108
– tuto108, Commented May 5, 2020 at 9:52
If this is a homework assignment, then please take note that your instincts are correct - this is NOT a proper job for a trigger, and the FK constraint is the correct solution to the actual business problem. I hate it when instructors do things like this. Use this assignment to learn what you can about how triggers work, but do not take it as a good example of how to solve the proposed business problem. — EdStevens
– EdStevens, Commented May 5, 2020 at 12:40

Littlefoot · Accepted Answer · 2020-05-05 10:17:17Z

1

You referenced ORDERS.ORDER_ID in a wrong manner; should have been :old.order_id. Also, should be row-level trigger as you can't reference :new or :old values in statement-level triggers.

create or replace trigger orders_order_items
  after delete on orders
  for each row                                        -- this
begin
    delete from order_items where order_items.order_id = :old.order_id;
                                                         ----
                                                      -- this
end;
/

edited May 5, 2020 at 10:17

answered May 5, 2020 at 9:54

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tuto108 Over a year ago

I get an error saying "NEW or OLD references not allowed in table level triggers" when I try this.

Littlefoot Over a year ago

So true; missing for each row as well. Fixed; have a look, please.

Collectives™ on Stack Overflow

How to delete a row using trigger in oraclesql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related