Oracle触发器实现数据变更日志-优快云博客

本文链接：https://blog.youkuaiyun.com/zyy123456789_123/article/details/101615463

这里写自定义目录标题

CREATE OR REPLACE TRIGGER “ZXX_TS”.“UPDATETST”
AFTER UPDATE --指定触发时机为修改操作后
ON ts
FOR EACH ROW --说明创建的是行级触发器
declare
v_num number:=0;
BEGIN
if :old.cs<:new.cs then

begin
<<while_loop>>
while (v_num<(:new.cs-:old.cs))
loop
–将修改前数据插入到日志记录表 del_emp ,以供监督使用。
INSERT INTO TST (pk,txm,ttxm,tslb,nyr,saas)
values( sys_guid(),nvl((SELECT replace(lpad(nvl(MAX(to_number(T.TXM)),000000)+1,6),’ ',‘0’) AS MAXTXM FROM TST T where t.saas=:new.saas),(select s.TXMPREFIX+1 from umsaas s where s.umsaascood=:new.saas)) ,:new.txm,:new.tslb,:new.nyr,:new.saas);
v_num:=v_num+1;
end loop while_loop;
end ;
end if ;

if :old.cs>:new.cs then

begin
<<while_loop>>
while (v_num<(:old.cs-:new.cs))
loop
INSERT INTO TSTbf (pk,txm,ttxm,tslb,nyr,saas,createtime)
values( sys_guid(),nvl((SELECT replace(lpad(nvl(MAX(to_number(T.TXM)),000000),6),’ ',‘0’) AS MAXTXM FROM TST T where t.saas=:new.saas and t.ttxm=:new.txm),(select s.TXMPREFIX+1 from umsaas s where s.umsaascood=:new.saas)) ,:new.txm,:new.tslb,:new.nyr,:new.saas,sysdate);

delete from tst t where t.saas=:new.saas and t.txm in ((SELECT replace(lpad(nvl(MAX(to_number(T.TXM)),000000),6),’ ',‘0’) AS MAXTXM FROM TST T where t.saas=:new.saas and t.ttxm=:new.txm)) and t.ttxm=:new.txm;
v_num:=v_num+1;
end loop while_loop;
end ;
end if ;
end;