各位GG:我现在想找到自某个时间以来,表中发生变化的数据(增,删,改),请问Oracle有没有什么功能可以实现?比如,有没有什么类似伪列,伪表之类的,自动标记记录发生改变的时间 ? 这样我就可以在select中加上 where time > LastUpdate嘻嘻~~~我对ORACLE一窍不通,还望各位GG帮忙~~~~
oracle 里面的触发器可以实现这样的功能。 creat or replace trigger tr_update_delete_insert after update or delete or insert on A // A表 你要删除的表 for each row declare daytime var; begin select to_char(sysdate,'YYYYMMDD HH24:MM:SS') into daytime from dual; if daytime >'20090909 09:09:09' then insert into B (name,add) values(:old.name,:old.add);// B表的列名跟A表一样,日志 end if; end —————— 没有编译
1.建立物化视图日志 CREATE MATERIALIZED VIEW LOG ON emp WITH PRIMARY KEY INCLUDING NEW VALUES;update emp set comm = 25 where empno = 7369;inert into emp ......select * from MLOG$_EMP9iSQL> select * from MLOG$_EMP; EMPNO SNAPTIME$ D O CHANGE_VECTOR$$ ---------- --------- - - ------------------------------ 8888 01-JAN-00 I N FEFF 7369 01-JAN-00 U U 8000 7369 01-JAN-00 U N 8000其中D列是DML Type,意思就是: I=insert U=update D=delete
creat or replace trigger tr_update_delete_insert
after update or delete or insert on A // A表 你要删除的表
for each row
declare
daytime var;
begin
select to_char(sysdate,'YYYYMMDD HH24:MM:SS') into daytime from dual;
if daytime >'20090909 09:09:09' then
insert into B (name,add) values(:old.name,:old.add);// B表的列名跟A表一样,日志
end if;
end
——————
没有编译
CREATE MATERIALIZED VIEW LOG ON emp
WITH PRIMARY KEY
INCLUDING NEW VALUES;update emp set comm = 25 where empno = 7369;inert into emp ......select * from MLOG$_EMP9iSQL> select * from MLOG$_EMP; EMPNO SNAPTIME$ D O CHANGE_VECTOR$$
---------- --------- - - ------------------------------
8888 01-JAN-00 I N FEFF
7369 01-JAN-00 U U 8000
7369 01-JAN-00 U N 8000其中D列是DML Type,意思就是: I=insert U=update D=delete