Oracle trigger before update

Question

I'm new to Oracle sql syntax. I want to change order_status_code of Orders, when Invoice's invoice_status_code was set to 1. However, when I change invoice_status_code, it prints error, which says Cannot update Orders because Shipment exists.

create or replace TRIGGER change_order_status
before update of invoice_status_code on Invoice
for each row
begin 
   if :new.invoice_status_code = 1 then 
      update Orders 
      set order_status_code = 1
      where order_id = :new.order_id;
    end if;
end;

This is how I update

update invoice 
set invoice_status_code = 1
where invoice_number = 2

Error message:

Error starting at line : 2 in command -
update invoice 
set invoice_status_code = 1
where invoice_number = 2
Error report -
ORA-20005: Cannot update Orders because Shipment exists.

Answer 1

Based on the second error in your comment, my guess is that there is a foreign key in shipment that references the (order_id, order_status_code) in orders. Since you have a row in shipment using that to refer to a row in orders, you cannot change the column in orders.

It seems odd that you have order_status_code in orders, shipment, and invoice. Assuming that is referring to the status of a single row in orders, I'd expect it to be in that table and no others.

P.S. User defined error codes are between ORA-20000 and ORA-20999. The first error stays in this range.