Create or replace trigger trigger_A after delete on A REFERENCING NEW AS NEW OLD AS OLD for each row
DECLARE
tmp varchar2(1000);
tmpval varchar2(1000);
val varchar2(4000);
begin if deleting then
tmp:=:OLD.ID;
tmpval:='delete from A where A.ID=';
val:=concat(tmpval,tmp);
insert into souformoper(sql,PROCESS_DATE) values(val,sysdate);
end if;
end trigger_A;
我写了一个删除的,可以记录下来。不过更新和插入,还没有做出来。参数多起来不好写,不知道有没有人做过这样的操作?有经验朋友交流一下吧
DECLARE
tmp varchar2(1000);
tmpval varchar2(1000);
val varchar2(4000);
begin if deleting then
tmp:=:OLD.ID;
tmpval:='delete from A where A.ID=';
val:=concat(tmpval,tmp);
insert into souformoper(sql,PROCESS_DATE) values(val,sysdate);
end if;
end trigger_A;
我写了一个删除的,可以记录下来。不过更新和插入,还没有做出来。参数多起来不好写,不知道有没有人做过这样的操作?有经验朋友交流一下吧
插入和删除之前先记录所有字段:
INSERT INTO SELECT * FROM TAB WHERE ....;
更新的话,只记录更新字段,
if updating('filename') then --判断是否更新此字段
--使用new值拼接sql
end if;
如果要得到insert语句,可以直接拼接new值就可以了
insert into souformoper(sql,PROCESS_DATE) values('insert into T_BAS_PRDCWB_INF(CONFIRM_ID,APPLY_ID,CONFIRM_STATE,COMM,OP_DATE,OP_USER_ID)valus('+:new.ID+','+:new.APPLY_ID+','+:new.CONFIRM_STATE+','+:new.comm+','+:new.op_date+','+:new.op_user_id+')',sysdate);
end if;我这样拼连,编译通过,但插入时并没有插入记录表
if inserting then
insert into visec_souformoper(id,sql,PROCESS_DATE) values('3','insert into T_BAS_PRDCWB_INF(CONFIRM_ID,APPLY_ID,CONFIRM_STATE,COMM,OP_DATE,OP_USER_ID)valus('+to_char(:new.CONFIRM_ID)+','+to_char(:new.APPLY_ID)+','+to_char(:new.CONFIRM_STATE)+','+to_char(:new.comm)+','+to_char(:new.op_date)+','+to_char(:new.op_user_id)+')',sysdate);
end if;