每天就是200条记录,对于这么小的数据量,还是对主表建立一个insert/update/delete的自治事务触发器吧。把所有的事情都记录下来。 例子(可以自己改改): create table log(ip varchar2(20),user varchar2(20),time date,what varchar2(200));CREATE OR REPLACE TRIGGER TRI_TRACE BEFORE INSERT OR DELETE OR UPDATE ON XXTABLE FOR EACH ROW BEGIN IF UPDATING THEN INSERT INTO LOG VALUES (SYS_CONTEXT('userenv', 'ip_address'), SYS_CONTEXT('USERENV', 'SESSION_USER'), SYSDATE, 'update xxtable,xxtable pk is '); ELSIF INSERTING THEN INSERT INTO LOG VALUES (SYS_CONTEXT('userenv', 'ip_address'), SYS_CONTEXT('USERENV', 'SESSION_USER'), SYSDATE, 'insert xxtable,xxtable pk is '); ELSIF DELETING THEN INSERT INTO LOG VALUES (SYS_CONTEXT('userenv', 'ip_address'), SYS_CONTEXT('USERENV', 'SESSION_USER'), SYSDATE, 'delete xxtable,xxtable pk is '); END IF; END; /
上面落下了点东西,修改之: create table log(ip varchar2(20),user varchar2(20),time date,what varchar2(200));CREATE OR REPLACE TRIGGER TRI_TRACE BEFORE INSERT OR DELETE OR UPDATE ON XXTABLE FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN IF UPDATING THEN INSERT INTO LOG VALUES (SYS_CONTEXT('userenv', 'ip_address'), SYS_CONTEXT('USERENV', 'SESSION_USER'), SYSDATE, 'update xxtable,xxtable pk is '); ELSIF INSERTING THEN INSERT INTO LOG VALUES (SYS_CONTEXT('userenv', 'ip_address'), SYS_CONTEXT('USERENV', 'SESSION_USER'), SYSDATE, 'insert xxtable,xxtable pk is '); ELSIF DELETING THEN INSERT INTO LOG VALUES (SYS_CONTEXT('userenv', 'ip_address'), SYS_CONTEXT('USERENV', 'SESSION_USER'), SYSDATE, 'delete xxtable,xxtable pk is '); END IF; COMMIT; EXCEPTION WHERE OTHERS THEN NULL; END; /
如果你录入这几条记录时确定提交commit过,那肯定就是在后来被delete掉了。
你建个 delete的触发器,把丢数据的记录记下来。
例子(可以自己改改):
create table log(ip varchar2(20),user varchar2(20),time date,what varchar2(200));CREATE OR REPLACE TRIGGER TRI_TRACE
BEFORE INSERT OR DELETE OR UPDATE ON XXTABLE
FOR EACH ROW
BEGIN
IF UPDATING THEN
INSERT INTO LOG
VALUES
(SYS_CONTEXT('userenv', 'ip_address'),
SYS_CONTEXT('USERENV', 'SESSION_USER'),
SYSDATE,
'update xxtable,xxtable pk is ');
ELSIF INSERTING THEN
INSERT INTO LOG
VALUES
(SYS_CONTEXT('userenv', 'ip_address'),
SYS_CONTEXT('USERENV', 'SESSION_USER'),
SYSDATE,
'insert xxtable,xxtable pk is ');
ELSIF DELETING THEN
INSERT INTO LOG
VALUES
(SYS_CONTEXT('userenv', 'ip_address'),
SYS_CONTEXT('USERENV', 'SESSION_USER'),
SYSDATE,
'delete xxtable,xxtable pk is ');
END IF;
END;
/
上面落下了点东西,修改之:
create table log(ip varchar2(20),user varchar2(20),time date,what varchar2(200));CREATE OR REPLACE TRIGGER TRI_TRACE
BEFORE INSERT OR DELETE OR UPDATE ON XXTABLE
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF UPDATING THEN
INSERT INTO LOG
VALUES
(SYS_CONTEXT('userenv', 'ip_address'),
SYS_CONTEXT('USERENV', 'SESSION_USER'),
SYSDATE,
'update xxtable,xxtable pk is ');
ELSIF INSERTING THEN
INSERT INTO LOG
VALUES
(SYS_CONTEXT('userenv', 'ip_address'),
SYS_CONTEXT('USERENV', 'SESSION_USER'),
SYSDATE,
'insert xxtable,xxtable pk is ');
ELSIF DELETING THEN
INSERT INTO LOG
VALUES
(SYS_CONTEXT('userenv', 'ip_address'),
SYS_CONTEXT('USERENV', 'SESSION_USER'),
SYSDATE,
'delete xxtable,xxtable pk is ');
END IF;
COMMIT;
EXCEPTION
WHERE OTHERS THEN
NULL;
END;
/