how to trigger an update on a table row value using a row value from another table in Apex Oracle SQL?

Question

i have two tables; Purchase order:

CREATE TABLE  "PURCHASE_ORDER" 
   (    "PO_NUMBER" NUMBER(*,0) NOT NULL ENABLE, 
    "CUSTOMER_NUMBER" NUMBER(*,0), 
    "PO_DATE" DATE, 
    "PRICE" NUMBER(*,0), 
    "ORDER_QUANTITY" NUMBER, 
    "STOCK_ID" NUMBER(*,0), 
     PRIMARY KEY ("PO_NUMBER")

and the bulk stock table:

CREATE TABLE  "BULK_STOCK" 
   (    "STOCK_ID" NUMBER(*,0) NOT NULL ENABLE, 
    "STOCK_DESCRIPTION" VARCHAR2(50), 
    "STOCK_UNITOF_MEASUREMENT" VARCHAR2(50), 
    "STOCK_STATUS" VARCHAR2(50), 
    "FLOOR_ID" NUMBER(*,0), 
    "STOCK_NAME" VARCHAR2(50), 
    "BULK_QUANTITY" NUMBER NOT NULL ENABLE, 
     PRIMARY KEY ("STOCK_ID")

I am creating a trigger that updates the BULK_QUANTITY in the bulk stock table when purchase table is inserted with values:

create or replace trigger "UPDATE_ON_PURCHASE"
BEFORE
insert or update or delete on "PURCHASE_ORDER"
for each row
begin
UPDATE bulk_stock
    SET BULK_QUANTITY =BULK_QUANTITY-:old.ORDER_QUANTITY
    WHERE STOCK_ID=:old.STOCK_ID;   
end;

when i run this form

Nothing changes in the bulk stock table

but if I hard code it

create or replace trigger "UPDATE_ON_PURCHASE"
BEFORE
insert or update or delete on "PURCHASE_ORDER"
for each row
begin
UPDATE bulk_stock
    SET BULK_QUANTITY =BULK_QUANTITY- 20 //the 20 would be the inserted ORDER_QUANTITY
    WHERE STOCK_ID= 12 // 12 would be the stock_ID

it works.

This is done in Oracle Apex software. I need help with the trigger. Many thanks end;

Answer 1

You're using the :OLD values in your trigger which seems problematic. On an INSERT the :OLD values are all NULL. So in the case of an INSERT, at the very least, it seems you'd want to use the :NEW values.

On an UPDATE the :OLD values contain the pre-update values. I don't know how your stock table is being maintained but it seems to me that you'd want to add the :OLD values back into stock then remove the :NEW values from stock, assuming that both the ORDER_QUANTITY and STOCK_ID can change.

When you're doing a DELETE the :OLD values contain the pre-deletion values, but the :NEW values are all NULL (makes sense, if you think about it). So in the case of a deletion it would seem that you'd want to use the :OLD values. However, if you're deleting a PO do you really want to adjust the stock? I'd think you'd need some type of status on the order to let you know if it's been fulfilled or cancelled or whatever, and only add the stock back into the bulk stock table if the order was never fulfilled.

In any case, one way to re-write your trigger would be:

create or replace trigger UPDATE_ON_PURCHASE
  BEFORE insert or update or delete on PURCHASE_ORDER
  for each row
  begin
    IF INSERTING THEN
      UPDATE bulk_stock
        SET BULK_QUANTITY = BULK_QUANTITY - :NEW.ORDER_QUANTITY
        WHERE STOCK_ID = :NEW.STOCK_ID; 
    ELSIF UPDATING THEN
      UPDATE BULK_STOCK
        SET BULK_QUANTITY = BULK_QUANTITY + :OLD.ORDER_QUANTITY
        WHERE STOCK_ID = :OLD.STOCK_ID; 

      UPDATE BULK_STOCK
        SET BULK_QUANTITY = BULK_QUANTITY - :NEW.ORDER_QUANTITY
        WHERE STOCK_ID = :NEW.STOCK_ID; 
    ELSIF DELETING THEN
      UPDATE BULK_STOCK
        SET BULK_QUANTITY = BULK_QUANTITY + :OLD.ORDER_QUANTITY
        WHERE STOCK_ID = :OLD.STOCK_ID;
    END IF;
  end;

I'm not sure that this logic is really what you wanted as I don't understand completely what you're trying to do, particularly in the DELETE case, so take it as an example and apply whatever logic your situation calls for.

I'll also say that I think putting this logic in a trigger is a bad choice. Business logic such as this should not be implemented in a trigger - better to put it into a procedure and call the procedure when needed. Putting business logic into triggers can be problematic.

Best of luck.