根据所要监控的表创建日志表

比如某业务表 UUC_BIZ_USER_BIND
在这里插入图片描述
新建日志表：

CREATE TABLE "UUC_BIZ_USER_BIND_LOG" 
   ("ID" NUMBER(8,0), 
	"MOBILE" VARCHAR2(255), 
	"ID_CARD" VARCHAR2(255), 
	"NAME" VARCHAR2(255), 
	"USER_ID" VARCHAR2(255), 
	"COM_CODE" VARCHAR2(255), 
	"COM_NAME" VARCHAR2(255), 
	"TEAM_CODE" VARCHAR2(255), 
	"TEAM_NAME" VARCHAR2(255), 
	"IS_SELL" CHAR(1), 
	"CHANNEL_SOURCE" CHAR(1), 
	"UPDATE_DATE" TIMESTAMP (6), 
	"SAPCOM_CODE" VARCHAR2(255), 
	"ISPCBUS" VARCHAR2(255), 
	"ROOT_DATE" VARCHAR2(255), 
	"PRACTFNO" VARCHAR2(255), 
	"ORG_CODE" VARCHAR2(255), 
	"CREATE_DATE" TIMESTAMP (6), 
	"TYPE" VARCHAR2(255), 
	 PRIMARY KEY ("ID"))

其中，区别于原表，新增了两个字段：
create_date 日志创建时间
type 类型：1、新增 2、修改 3、删除

创建序列，用于日志表ID自增

create sequence SEQ_USER_BIND_LOG 
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

创建触发器，用于监控表数据变更并记录日志表

CREATE OR REPLACE TRIGGER TRIGGER_USER_BIND_UPDATE 

BEFORE INSERT OR UPDATE OR DELETE ON UUC_BIZ_USER_BIND

FOR EACH ROW

DECLARE 

V_ID	NUMBER(8);
V_MOBILE	VARCHAR2(255);
V_ID_CARD	VARCHAR2(255);
V_NAME	VARCHAR2(255);
V_USER_ID	VARCHAR2(255);
V_COM_CODE	VARCHAR2(255);
V_COM_NAME	VARCHAR2(255);
V_TEAM_CODE	VARCHAR2(255);
V_TEAM_NAME	VARCHAR2(255);
V_IS_SELL	CHAR(1);
V_CHANNEL_SOURCE	CHAR(1);
V_UPDATE_DATE	TIMESTAMP(0);
V_SAPCOM_CODE	VARCHAR2(255);
V_ISPCBUS	VARCHAR2(255);
V_ROOT_DATE	VARCHAR2(255);
V_PRACTFNO	VARCHAR2(255);
V_ORG_CODE	VARCHAR2(255);
V_CREATE_DATE	TIMESTAMP(0);
V_TYPE	VARCHAR2(255);

BEGIN

/*

修改时往历史表中存放的是修改前的数据.

删除时往历史表中存放的是删除前的数据.

*/

SELECT SEQ_USER_BIND_LOG.NEXTVAL INTO V_ID FROM DUAL; -- 利用seq生成主键

V_MOBILE		:=		:OLD.MOBILE;
V_ID_CARD		:=		:OLD.ID_CARD;
V_NAME		:=		:OLD.NAME;
V_USER_ID		:=		:OLD.USER_ID;
V_COM_CODE		:=		:OLD.COM_CODE;
V_COM_NAME		:=		:OLD.COM_NAME;
V_TEAM_CODE		:=		:OLD.TEAM_CODE;
V_TEAM_NAME		:=		:OLD.TEAM_NAME;
V_IS_SELL		:=		:OLD.IS_SELL;
V_CHANNEL_SOURCE		:=		:OLD.CHANNEL_SOURCE;
V_UPDATE_DATE		:=		:OLD.UPDATE_DATE;
V_SAPCOM_CODE		:=		:OLD.SAPCOM_CODE;
V_ISPCBUS		:=		:OLD.ISPCBUS;
V_ROOT_DATE		:=		:OLD.ROOT_DATE;
V_PRACTFNO		:=		:OLD.PRACTFNO;
V_ORG_CODE		:=		:OLD.ORG_CODE;

IF INSERTING THEN

V_TYPE		:=		'1';

V_MOBILE		:=		:NEW.MOBILE;
V_ID_CARD		:=		:NEW.ID_CARD;
V_NAME		:=		:NEW.NAME;
V_USER_ID		:=		:NEW.USER_ID;
V_COM_CODE		:=		:NEW.COM_CODE;
V_COM_NAME		:=		:NEW.COM_NAME;
V_TEAM_CODE		:=		:NEW.TEAM_CODE;
V_TEAM_NAME		:=		:NEW.TEAM_NAME;
V_IS_SELL		:=		:NEW.IS_SELL;
V_CHANNEL_SOURCE		:=		:NEW.CHANNEL_SOURCE;
V_UPDATE_DATE		:=		:NEW.UPDATE_DATE;
V_SAPCOM_CODE		:=		:NEW.SAPCOM_CODE;
V_ISPCBUS		:=		:NEW.ISPCBUS;
V_ROOT_DATE		:=		:NEW.ROOT_DATE;
V_PRACTFNO		:=		:NEW.PRACTFNO;
V_ORG_CODE		:=		:NEW.ORG_CODE;

ELSIF UPDATING THEN

V_TYPE		:=		'2';

ELSE

V_TYPE		:=		'3';

END IF;

INSERT INTO UUC_BIZ_USER_BIND_LOG(ID,MOBILE,ID_CARD,NAME,USER_ID,COM_CODE,COM_NAME,TEAM_CODE,TEAM_NAME,IS_SELL,CHANNEL_SOURCE,UPDATE_DATE,SAPCOM_CODE,ISPCBUS,ROOT_DATE,PRACTFNO,ORG_CODE,CREATE_DATE,TYPE)

VALUES(V_ID,V_MOBILE,V_ID_CARD,V_NAME,V_USER_ID,V_COM_CODE,V_COM_NAME,V_TEAM_CODE,V_TEAM_NAME,V_IS_SELL,V_CHANNEL_SOURCE,V_UPDATE_DATE,V_SAPCOM_CODE,V_ISPCBUS,V_ROOT_DATE,V_PRACTFNO,V_ORG_CODE, SYSDATE,V_TYPE);

END;