oracle 触发器update保留原数据库,ORACLE简单的更新update触发器

create or replace TRIGGER declhead_up_declList

AFTER UPDATE ON DECLARATION_HEAD FOR EACH ROW

declare

PARTS_NOSS varchar2(32);

ENTRY_ID_S varchar2(256);

BEGIN

select ltrim(max(sys_connect_by_path(f.ENTRY_ID,','))

keep (dense_rank last order by f.pnum),',') as ENTRY_ID  into ENTRY_ID_S

from(    select t.order_no

,t.ENTRY_ID

,row_number() over(partition by t.order_no  order by t.order_no) as pnum

,row_number() over(partition by t.order_no  order by t.order_no)-1 as lnum

from

(

select a.order_no,a.ENTRY_ID

from DECLARATION_HEAD  a where order_no=:old.ORDER_NO

) t

)f

group by f.order_no

connect by f.lnum = prior f.pnum and f.order_no = prior f.order_no

start with f.pnum = 1;

IF instr(:new.BILL_NO,'/',1)>0

then PARTS_NOSS:=substr(:new.BILL_NO,instr(:new.BILL_NO,'/',-1)+1);

else PARTS_NOSS:='';

end if;

update DECLSTATE_LIST

set ORDER_NO=:new.ORDER_NO,--订单编号

PO_NO=:new.CONTRACT_NO,--PO_no

INVOICE_NO=:new.INVOICE_NO,--发票号

--SUPPLIER--供应商

USERSIDE=:new.TRADING_NAME,--使用方法(消费使用单位/生产销售单位名称)

TRADE_CLAUSE=(select DESCRIPTION from transac where code=:new.TRANS_MODE_CODE and rownum=1),----贸易条款(成交方式)

TYPE_SHIPPING=(select DESCRIPTION from transport where code=:new.TRAF_mode_code and rownum=1),--运输方式(运输工具名称)

MAIN_NO=:new.BILL_NO,--主单号

PARTS_NO=PARTS_NOSS,--分单号

FLIGHT_NUMBER=:new.VOYAGE_NO,--航班/船名航次

--DECL_NO_MAIN=:new.ENTRY_ID,--报关单号

DECL_NO_MAIN=ENTRY_ID_S,--报关单号

TRADE_TYPE=(select DESCRIPTION from mt_trade where code=:new.TRADE_MODE_CODE and rownum=1),--贸易方式

PACK_NO=:new.PACK_NO,--件数

GROSS_WT=:new.GROSS_WT,--毛重

--体积

TOTAL=:new.TOTAL,--货值

CURRENCY_CODE=:new.FEE_CURR_CODE,-- 币种

--运费申报

TRADE_COUNTRY=:new.TRADE_COUNTRY_CODE,--起运国

DISTINATE_PORT=:new.DISTRICT_CODE--目的港

where ORDER_NO=:old.ORDER_NO;

update EVENT_LIST

set DECL_NO=:new.ENTRY_ID

where DECL_NO=:old.ENTRY_ID;

END;