俺寫了一段trigger如下
CREATE OR REPLACE TRIGGER CPT.spc3_idd1
after inserton CPT.paneltestqcdata1
for each row
declare
m_prod_nbrvarchar2(7);
m_cnt
number(1);
m_inbasevarchar2(3);
m_pnl_idvarchar2(9);
begin
if inserting then
select substr(b.prod_id,5,7),substr(b.prod_id,18,3) into m_prod_nbr,m_inbase from lot a,brmproduct b where a.prod_key=b.prod_key and a.lot_id=:new.panel_id;
select cnt,pnl_id into m_cnt,m_pnl_id from spc3_iddslts where prod_nbr=m_prod_nbr;
insert into spc3_idd (pnl_id,inbase,PROD_NBR,TXN_TIME,IDD1)
values (:new.panel_id,m_inbase,m_prod_nbr,TO_DATE(:new.create_datetime,'YYYY/MM/DD HH24:MI:SS'),:new.idd);
insert into spc3_iddslts (pnl_id,prod_nbr,cnt)
values (:new.panel_id,m_prod_nbr,1);
END IF;
end spc3_idd1;
因為select cnt,pnl_id into m_cnt,m_pnl_id from spc3_iddslts where prod_nbr=m_prod_nbr;[/COLOR] 會找不到數據,所以下面的兩句insert 指令都不會執行到.
好像可以用
exception
when no_data_found then
....
end
這樣的方式可以忽略null繼續執行insert的功能.
具體的寫法,插在程序的哪裡就請教大家了,謝謝!
|
