drop table stock_levels;
create table stock_levels (

  scu                    number primary key,
  threshold_stock_level  number,
  target_stock_level     number,
  curr_stock_level       number,
  vendor_id              number );


insert into stock_levels
  ( scu, threshold_stock_level, target_stock_level, curr_stock_level, vendor_id )
  values
  ( 1, 10, 50, 12, 1 );
commit;


create or replace trigger Stock_Low
  after update of curr_stock_level
  on stock_levels
  for each row
declare
  v_order_ref integer;

begin
  if :new.curr_stock_level < :new.threshold_stock_level
  then
    select order_ref_seq.nextval
      into v_order_ref
      from dual;
    insert into customer_orders ( order_ref, vendor_id, scu, quantity )
      values

      ( v_order_ref,
        :new.vendor_id,
        :new.scu,
        ( :new.target_stock_level - :new.curr_stock_level ) );
  end if;
end Stock_Low;
/
Show Errors