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;

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.

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.