--日志记录表
CREATE TABLE T_LOG
(
RID VARCHAR2(32 BYTE),
NAME VARCHAR2(1000 BYTE),
RQ DATE DEFAULT sysdate,
OLDVALUE VARCHAR2(255 BYTE),
NEWVALUE VARCHAR2(255 BYTE)
);
CREATE INDEX IDX_T_LOG_RID ON T_LOG (RID);
--目标表
CREATE TABLE T_TARGET
(
id char(32 BYTE),
NAME VARCHAR2(20 BYTE),
salary number,
birthday date
);
alter table T_TARGET add constraint primary key (id);
--触发器
CREATE OR REPLACE TRIGGER TR_TARGET_MODIFYLOG
AFTER UPDATE
ON T_TARGET
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
BEGIN
if :new.birthday<>:old.birthday
or nvl2(:new.birthday,1,0)+nvl2(:old.birthday,1,0)=1
then
insert into T_LOG (rid,name,newvalue,oldvalue)
values(:new.id,'生日',
to_char(:new.birthday,'yyyy-mm-dd'),
to_char(:old.birthday,'yyyy-mm-dd'));
end if;
if :new.name<>:old.name
or nvl2(:new.name,1,0)+nvl2(:old.name,1,0)=1
then
insert into t_log (rid,name,newvalue,oldvalue)
values(:new.id,'姓名',:new.name,:old.name);
end if;
if :new.salary<>:old.salary
or nvl2(:new.salary,1,0)+nvl2(:old.salary,1,0)=1
then
insert into t_log (rid,name,newvalue,oldvalue)
values(:new.id,'工资',:new.salary,:old.salary);
end if;
END TR_TARGET_MODIFYLOG;
/
Oracle数据库使用触发器记录表数据修改记录
最新推荐文章于 2021-04-07 05:41:53 发布