create or replace trigger XETC_POI_INS_POH_MARK1
before insert on PO_HEADERS_ALL
for each row
declare
V_HEADER_ID PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
V_ATTRIBUTE1 PO_HEADERS_ALL.ATTRIBUTE1%TYPE;
V_VENDOR_ID PO_HEADERS_ALL.VENDOR_ID%TYPE;
begin
V_HEADER_ID:=:NEW.PO_HEADER_ID;
SELECT PH.VENDOR_ID
INTO V_VENDOR_ID
FROM PO_HEADERS_ALL PH
WHERE PH.PO_HEADER_ID=:NEW.PO_HEADER_ID
AND PH.ORG_ID=46;
SELECT PV.ATTRIBUTE1
INTO V_ATTRIBUTE1
FROM PO_VENDORS PV
WHERE PV.VENDOR_ID=V_VENDOR_ID
AND PV.SET_OF_BOOKS_ID=3;
:NEW.ATTRIBUTE1:=V_ATTRIBUTE1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- Consider logging the error and then re-raise
DBMS_OUTPUT.put_line('PLEASE INPUT UNIT PRICE BY MANUAL');
end XETC_POI_INS_POH_MARK1;
最初由 hotwish 发布
[B]create or replace trigger XETC_POI_INS_POH_MARK1
before insert on PO_HEADERS_ALL
for each row
declare
V_HEADER_ID PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
V_ATTRIBUTE1 PO_HEADERS_ALL.ATTRIBUTE1%TYPE;
V_VENDOR_ID PO_HEADERS_ALL.VENDOR_ID%TYPE;
begin
V_HEADER_ID:=:NEW.PO_HEADER_ID;
SELECT PH.VENDOR_ID
INTO V_VENDOR_ID
FROM PO_HEADERS_ALL PH
WHERE PH.PO_HEADER_ID=:NEW.PO_HEADER_ID
AND PH.ORG_ID=46;
SELECT PV.ATTRIBUTE1
INTO V_ATTRIBUTE1
FROM PO_VENDORS PV
WHERE PV.VENDOR_ID=V_VENDOR_ID
AND PV.SET_OF_BOOKS_ID=3;
:NEW.ATTRIBUTE1:=V_ATTRIBUTE1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- Consider logging the error and then re-raise
DBMS_OUTPUT.put_line('PLEASE INPUT UNIT PRICE BY MANUAL');
end XETC_POI_INS_POH_MARK1; [/B]
最初由 hotwish 发布
[B]預期效果為,希望在建立po單時候自動將po_vendor.attribute1的信息賽到po_headers_all.attribute1
為什麼我寫的trigger沒有辦法實現,請高手支招,謝謝 [/B]
楼上的,在FOR EACHROW前面加上一句REFERENCING NEW AS NEW OLD AS OLD
试试看
如果不行看看TRIGGER是不是有效的,编译一下,我试了是可以的
最初由 三毛aa 发布
[B]
楼上的,在FOR EACHROW前面加上一句REFERENCING NEW AS NEW OLD AS OLD
试试看
如果不行看看TRIGGER是不是有效的,编译一下,我试了是可以的 [/B]
有加REFERENCING NEW AS NEW OLD AS OLD,and recompile sucessfully
但是沒有講po_vendors.attribute1資料帶到po上呀
SELECT PH.VENDOR_ID
INTO V_VENDOR_ID
FROM PO_HEADERS_ALL PH
WHERE PH.PO_HEADER_ID=:NEW.PO_HEADER_ID
AND PH.ORG_ID=46;
问题出在这吧,既然是before insert,po_headers_all里就没有这条记录,怎么能这么取呢?
最初由 smallworm 发布
[B]SELECT PH.VENDOR_ID
INTO V_VENDOR_ID
FROM PO_HEADERS_ALL PH
WHERE PH.PO_HEADER_ID=:NEW.PO_HEADER_ID
AND PH.ORG_ID=46;
问题出在这吧,既然是before insert,po_headers_all里就没有这条记录,怎么能这么取呢? [/B]
好眼力
所以你找你的vendor attribute1的时候用:new.vendor_id做条件就好了:)