create or replace trigger tri_t_interface_update
after update on t_interface
for each row
begin
insert into t_interface_his(DATA_SOURCE,SERIAL_NBR,SUB_SERIAL_NBR)
values (:NEW.DATA_SOURCE,:NEW.SERIAL_NBR,:NEW.SUB_SERIAL_NBR); delete t_interface
where serial_nbr = :new.serial_nbr
and sub_serial_nbr = :new.sub_serial_nbr;end;其中serial_nbr和sub_serial_nbr 构成主键.
T_Interface和T_Interface_His结构完全相同.
after update on t_interface
for each row
begin
insert into t_interface_his(DATA_SOURCE,SERIAL_NBR,SUB_SERIAL_NBR)
values (:NEW.DATA_SOURCE,:NEW.SERIAL_NBR,:NEW.SUB_SERIAL_NBR); delete t_interface
where serial_nbr = :new.serial_nbr
and sub_serial_nbr = :new.sub_serial_nbr;end;其中serial_nbr和sub_serial_nbr 构成主键.
T_Interface和T_Interface_His结构完全相同.
因此此错误正常
变通一下
create or replace trigger tri_t_interface_update
after update on t_interface
for each row
begin
insert into t_interface_his(DATA_SOURCE,SERIAL_NBR,SUB_SERIAL_NBR)
values (:NEW.DATA_SOURCE,:NEW.SERIAL_NBR,:NEW.SUB_SERIAL_NBR);
:new.SUB_SERIAL_NBR:='xxxxxx';//给数据加标记,供以后删除
end;
VARIABLE jobno number;
begin
DBMS_JOB.SUBMIT(:jobno,
'delete from t_interface where SUB_SERIAL_NBR='xxxxxx';',
SYSDATE, 'SYSDATE + 1/720');//每隔两分钟清除一次
commit;
end;
/