create or replace trigger 上料工單表基本規(guī)則
  before  insert  
  on ITEM_ORDER_BIND_NEW
  for each row
declare
    v_cnt int;
    v_id ITEM_RESOURCE_BIND_NEW."KEY"%type;
    v_INVENTORY ITEM_RESOURCE_BIND_NEW.INVENTORY%type;

begin    
    SELECT max(ID) into v_id  FROM ITEM_RESOURCE_BIND_NEW  WHERE "KEY" = :new.BINDKEY;
    SELECT INVENTORY into v_INVENTORY  FROM ITEM_RESOURCE_BIND_NEW  WHERE id = v_id;
    
    SELECT count(*) into v_cnt FROM INVENTORY i  WHERE SITE = :new.SITE AND INVENTORY= v_INVENTORY and SHOP_ORDER like '%'||:new.SHOP_ORDER||'%';
     if v_cnt = 0 then
        raise_application_error(-20001, '條碼'||v_INVENTORY||'指定工單和選擇的工單不一致');
    end if;

end 上料工單表基本規(guī)則;

create or replace trigger 上料工單表基本規(guī)則
  before  insert  
  on ITEM_ORDER_BIND_NEW
  for each row
declare
    v_cnt                 int;
    v_id                  ITEM_RESOURCE_BIND_NEW."KEY"%type;
    v_inventory           item_resource_bind_new.inventory%type;
    v_error_proofing_sign item.error_proofing_sign%type;
    v_item                inventory.item%type;
begin
    select max(id) into v_id from item_resource_bind_new where "KEY" = :new.bindkey;
    select inventory, item into v_inventory, v_item from item_resource_bind_new where id = v_id;
    select count(*)
    into v_cnt
    from inventory i
    where site = :new.site
          and inventory = v_inventory
          and shop_order like '%' || :new.shop_order || '%';
    select error_proofing_sign
    into v_error_proofing_sign
    from item i
    where site = :new.site
          and item = v_item;
    if v_error_proofing_sign = 'P'
       and v_cnt = 0 then
        raise_application_error(-20001, '條碼' || v_inventory || '指定工單和選擇的工單不一致');
    end if;
end 上料工單表基本規(guī)則;