oracle数据变化触发,Oracle数据库使用触发器记录表数据修改记录

--日志记录表

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;

/