关于触发器无法更新多行问题

qq_38187169 2017-04-05 10:17:52

单一品号当状态更新后可更新，多个品号(多行)无法更新，触发器如下：
create or replace trigger AA_PR_LINES_UPDATE_T1
after insert or update on "PO"."PO_REQUISITION_HEADERS_ALL"
referencing new as new
for each row
when (new.AUTHORIZATION_STATUS='APPROVED' and
new.requisition_header_id is not null)
begin
declare
p_header_id number;
p_line_id number;
p_line_num number;
ax number;
bx number;
p_item_id NUMBER;
begin
select prl.item_id,prl.blanket_po_header_id,prl.blanket_po_line_num,prl.requisition_line_id
into p_item_id,ax,bx,p_line_id
from po_requisition_lines_all prl
where prl.requisition_header_id=:new.requisition_header_id;
select po_header_id,line_num into p_header_id,p_line_num
from (select ROW_NUMBER() OVER(ORDER BY pol.unit_price) rownumber
,poh.po_header_id,pol.line_num,s.vendor_name,ass.vendor_site_code,poh.vendor_id
,ass.vendor_site_id,poh.segment1,pol.unit_price,pol.attribute1,pol.attribute2
,pol.item_id
from po_headers_all poh,po_lines_all pol,ap_suppliers s,ap_supplier_sites_all ass
where poh.po_header_id = pol.po_header_id
and poh.type_lookup_code = 'BLANKET'
and pol.item_id =p_item_id
and poh.approved_flag = 'Y'
and NVL(pol.closed_code, 'OPEN') = 'OPEN'
and pol.creation_date || '-' || poh.vendor_id in (select max(pol.creation_date) || '-' || poh.vendor_id
from po_headers_all poh,
po_lines_all pol
where poh.po_header_id = pol.po_header_id
and poh.type_lookup_code = 'BLANKET'
and pol.item_id = p_item_id
and NVL(pol.closed_code,'OPEN') = 'OPEN'
and nvl(pol.cancel_flag,'N') = 'N'
and poh.approved_flag = 'Y'
group by poh.vendor_id)
and nvl(pol.cancel_flag, 'N') = 'N'
and poh.vendor_id=s.vendor_id
and s.vendor_id=ass.vendor_id)
where 1 = 1
and rownumber = 1;

if ax<>p_header_id /*and bx<>p_line_num*/ then
update PO_REQUISITION_LINES_ALL prl
set prl.blanket_po_header_id=p_header_id
,prl.blanket_po_line_num=p_line_num

where prl.requisition_header_id=:new.requisition_header_id
and prl.item_id=p_item_id
and prl.requisition_line_id=p_line_id;
commit;
end if;

exception
when others then null;
end;
end;
请高手帮忙看下哪里写错了。

...全文