Trigger在用:New.col的時候獲取值出問題

Question

create or replace trigger MGF004_WIP_DISCRETE_JOBS
  before insert on WIP_REQUIREMENT_OPERATIONS  
  for each row
declare
  L_SUPPLY_SUBINV  VARCHAR2(20);
  L_SUPPLY_LOCATOR  VARCHAR2(20);  
   l_num  VARCHAR2(20) :='0';--
   l_error_msg  VARCHAR2(200) ;--
begin
 ------1. 結果顯示這段SQL得出L_num=1----------------
  SELECT count(1)  INTO L_num          
  FROM WIP_LINES WL,WIP_DISCRETE_JOBS WDJ
  WHERE WL.LINE_ID=WDJ.LINE_ID
  AND WDJ.ORGANIZATION_ID=121    
  AND WDJ.WIP_ENTITY_ID=7024;---結果顯示l_error_msg1 為空
l_error_msg1 =:NEW.ORGANIZATION_ID || :NEW.WIP_ENTITY_ID;---------下面這段SQL執行的時候報錯，ORA-01403: no data found，select不出任何記錄--------
  SELECT nvl(WL.ATTRIBUTE1,'NULL'), nvl(WL.ATTRIBUTE2,'NULL')
  INTO L_SUPPLY_SUBINV,L_SUPPLY_LOCATOR
  FROM WIP_LINES WL,WIP_DISCRETE_JOBS WDJ
  WHERE WL.LINE_ID=WDJ.LINE_ID
  AND WDJ.ORGANIZATION_ID=nvl(:NEW.ORGANIZATION_ID,121)    --Insert的值是121
  AND WDJ.WIP_ENTITY_ID=nvl(:NEW.WIP_ENTITY_ID,7024);      --Insert的值是7024
________________________________________________________________________________________
  
  l_num  :='5';--
  IF :NEW.SUPPLY_SUBINVENTORY='LT1120' and L_SUPPLY_SUBINV!='NULL' THEN  
     :NEW.SUPPLY_SUBINVENTORY:=L_SUPPLY_SUBINV;
  END IF;
  l_num :='6';--
  IF :NEW.SUPPLY_Locator_id  IS NULL AND L_SUPPLY_LOCATOR!='NULL' THEN
     :NEW.SUPPLY_Locator_id:=L_SUPPLY_LOCATOR;   
  END IF;
  exception when others then
  l_error_msg:= substr(SQLERRM,1,80);
  insert into xx_test12(numb,error_msg) values(l_error_msg1,to_char(L_num2) ||'_'||to_char(:NEW.WIP_ENTITY_ID) ||':'|| to_char(:NEW.ORGANIZATION_ID)||'::'|| l_error_msg);
end MGF004_WIP_DISCRETE_JOBS;報錯是：最後結果是：xx_test12里第一列為空，第二列為：1_7030:121::ORA-01403: no data 

Answer 1

有什么问题呢，因为
SELECT nvl(WL.ATTRIBUTE1,'NULL'), nvl(WL.ATTRIBUTE2,'NULL')
INTO L_SUPPLY_SUBINV,L_SUPPLY_LOCATOR
FROM WIP_LINES WL,WIP_DISCRETE_JOBS WDJ
WHERE WL.LINE_ID=WDJ.LINE_ID
AND WDJ.ORGANIZATION_ID=nvl(:NEW.ORGANIZATION_ID,121) --Insert的值是121
AND WDJ.WIP_ENTITY_ID=nvl(:NEW.WIP_ENTITY_ID,7024); --Insert的值是7024
没有符合条件的纪录，那么当然会走exception when others ...
触发器没有问题，执行正常
如果楼主需要在没有符合条件的纪录的时候有其他的处理，可以增加exception，例如：
exception 
when no_data_found then 
--没有查询到数据后的处理
...
when others then 
--其他错误
...
end;

Answer 2

謝謝xiaoxiao1984(笨猫儿）！
可能是我表達有點問題，我的問題在於兩段SQL,為何第一個可以select出來結果，而第二段沒有：
  SELECT nvl(WL.ATTRIBUTE1,'NULL'), nvl(WL.ATTRIBUTE2,'NULL')
  INTO L_SUPPLY_SUBINV,L_SUPPLY_LOCATOR
  FROM WIP_LINES WL,WIP_DISCRETE_JOBS WDJ
  WHERE WL.LINE_ID=WDJ.LINE_ID
  AND WDJ.ORGANIZATION_ID=121
  AND WDJ.WIP_ENTITY_ID=7024  SELECT nvl(WL.ATTRIBUTE1,'NULL'), nvl(WL.ATTRIBUTE2,'NULL')
  INTO L_SUPPLY_SUBINV,L_SUPPLY_LOCATOR
  FROM WIP_LINES WL,WIP_DISCRETE_JOBS WDJ
  WHERE WL.LINE_ID=WDJ.LINE_ID
  AND WDJ.ORGANIZATION_ID=nvl(:NEW.ORGANIZATION_ID,121)    --Insert的值是121
  AND WDJ.WIP_ENTITY_ID=nvl(:NEW.WIP_ENTITY_ID,7024);      --Insert的值是7024在最後面的Exception里有記錄:NEW.ORGANIZATION_ID和:NEW.WIP_ENTITY_ID到測試表，顯示結果分別就是寫死的121和7024。
寫死的值就有問題，用:NEW.ORGANIZATION_ID和:NEW.WIP_ENTITY_ID就會select不出結果是我的困擾所在啦~~~

Answer 3

把
  SELECT nvl(WL.ATTRIBUTE1,'NULL'), nvl(WL.ATTRIBUTE2,'NULL')
  INTO L_SUPPLY_SUBINV,L_SUPPLY_LOCATOR
  FROM WIP_LINES WL,WIP_DISCRETE_JOBS WDJ
  WHERE WL.LINE_ID=WDJ.LINE_ID
  AND WDJ.ORGANIZATION_ID=nvl(:NEW.ORGANIZATION_ID,121)    --Insert的值是121
  AND WDJ.WIP_ENTITY_ID=nvl(:NEW.WIP_ENTITY_ID,7024);      --Insert的值是7024
改为
  SELECT nvl(WL.ATTRIBUTE1,'NULL'), nvl(WL.ATTRIBUTE2,'NULL')
  INTO L_SUPPLY_SUBINV,L_SUPPLY_LOCATOR
  FROM WIP_LINES WL,WIP_DISCRETE_JOBS WDJ
  WHERE WL.LINE_ID=WDJ.LINE_ID
  AND WDJ.ORGANIZATION_ID=121
  AND WDJ.WIP_ENTITY_ID=7024;
再看看是否没找到记录。

Answer 4

SELECT nvl(WL.ATTRIBUTE1,'NULL'), nvl(WL.ATTRIBUTE2,'NULL')
  INTO L_SUPPLY_SUBINV,L_SUPPLY_LOCATOR
  FROM WIP_LINES WL,WIP_DISCRETE_JOBS WDJ
  WHERE WL.LINE_ID=WDJ.LINE_ID
  AND WDJ.ORGANIZATION_ID=121
  AND WDJ.WIP_ENTITY_ID=7024
這樣有記錄，不知道會不會是環境有問題。

Answer 5

可能是类型不同的问题么？
尝试一下直接判断nvl(:NEW.ORGANIZATION_ID,121)是否等于121加一段代码
if nvl(:NEW.ORGANIZATION_ID,121) = 121 then  
dbms_output.put_line(...);
else
dbms_output.put_line(...);
end if;if ...然后再次执行一次,看看输出里面是怎么回事么

Answer 6

楼主用的before,在插入的时候用:new不能取到值吧?如果想取插入的新值,trigger应该用after动作

Answer 7

kk:=2;
if :NEW.ORGANIZATION_ID = 121 then  
kk:=3;
end if;
結果是kk=3,就是:NEW.ORGANIZATION_ID 是等於121的
To ColinGan(浪子):
before,在插入的时候用:new不能取到值吧--好像有道理，但是在有的地方又可以取到它的值，所以奇怪，如果改成After的話，我後面有對insert列的更改，所以必須是before模式

Answer 8

明天可以架起新環境，到時候把Trigger放到新環境測試測試就知道了，如果是環境問題，就浪費大家的時間了，發上來是看看有沒有類似的問題。謝謝！

Answer 9

呵呵，before触发器是可以在插入前取得值的，应该和这个没有关系
在oracle 9.2.0.1 for win 32bit上做了一个测试create or replace trigger testbefore
  before insert on testb 
  for each row
declare
  -- local variables here
begin
  dbms_output.put_line(:new.id ||'---------' ||:new.sdate);
end testbefore;SQL> insert into testb(id, sdate) values(125151, sysdate);125151---------2006-11-07 16:52:461 row inserted觉得应该不是before insert 的问题

Answer 10

可能是数据类型的问题。做了如下实验
SQL> create table t_test1(c1 char(5));Table createdSQL> insert into t_test1 values('2    ');1 row insertedSQL> commit;Commit complete执行下面SQL后的结果是
set serveroutput on size 1000000
declare
  v1 number:=2;
  v2 varchar2(5):=2;
  v3 char(5):=2;
  i number;
begin
  select count(*) into i from t_test1 where c1=v1;
  dbms_output.put_line('where c1=v1 , v1='||v1||', count(*)='||i);
  select count(*) into i from t_test1 where c1=v2;
  dbms_output.put_line('where c1=v2 , v2='||v2||', count(*)='||i);
  select count(*) into i from t_test1 where c1=v3;
  dbms_output.put_line('where c1=v3 , v3='||v3||', count(*)='||i);
end;
/SQL> where c1=v1 , v1=2, count(*)=1
where c1=v2 , v2=2, count(*)=0
where c1=v3 , v3=2    , count(*)=1PL/SQL procedure successfully completedSQL> drop table t_test1;Table droppedSQL>

Answer 11

是有道理，我感覺就是這個原因，但是：
WIP_DISCRETE_JOBS.ORGANIZATION_ID，
WIP_DISCRETE_JOBS.WIP_ENTITY_ID，
WIP_REQUIREMENT_OPERATIONS.ORGANIZATION_ID,  
WIP_REQUIREMENT_OPERATIONS.WIP_ENTITY_ID
這四個都是Number型的

Answer 12

1、l_error_msg1为空的原因是因为你把两个数值型的用||连接，这样写是错误的，应该至少第一个值要用to_char函数转换。
2、xx_test12里第二列為：1_7030:121::ORA-01403: no data，说明触发时:NEW.WIP_ENTITY_ID不是7024，而是7030。

Answer 13

謝謝！
已經知道怎么解了，郁悶了這麼久，終於可以結了。
其實這是一個邏輯的問題：
涉及三個表：
線別表（wip_lines)，工單表(WIP_DISCRETE_JOBS)和扣料信息表WIP_REQUIREMENT_OPERATIONS)
在一個工單保存的時候，
我Trigger邏輯是：當往扣料表里Insert的時候，從通過工單關聯到扣料表並獲取扣料的信息。而實際上的工單邏輯可能是：在往扣料表里Insert的時候，工單並沒有保存。
雖然郁悶了，但是也知道了~~~~~
呵呵！
非常感謝bobfang(匆匆过客)和xiaoxiao1984(笨猫儿) ！