基于触发器实现数据变更记录与重做的功能实现

前提条件：

1. 确保每张表对有设置唯一的主键，暂不支持联合主键；

2. 确保数据库用户能访问Oracle的系统表；

3. 系统无外键设置；

4. 系统对事物强一致性没有太高的要求；

5. 变更记录库与重做库用户能

准备工作

• pl/sql连接数据库；
• 检测系统表是否都设置主键，可参考如下存储过程checkNoPKTable

  create or replace procedure checkNoPKTable is
    str varchar2(1000);
  begin
    for v in (SELECT *
                FROM user_tables ut
               where not exists (select 1
                        from user_constraints au
                       where ut.TABLE_NAME = au.table_name
                         and au.constraint_type = 'P')) loop
      begin
        str := ' table ' || v.table_name || ' not set Pk, Error';
        dbms_output.put_line('sql:' || str);
      end;
    end loop;
  end checkNoPKTable;
  

   

• 创建日志记录表，data_op_log

--drop table
drop table DATA_OP_LOG;	
-- Create table
create table DATA_OP_LOG
(
  id           VARCHAR2(32) not null,
  target_table VARCHAR2(512) not null,
  reference_id VARCHAR2(32) not null,
	reference_column VARCHAR2(128) not null,
  op_type      VARCHAR2(1) not null,
  op_time      TIMESTAMP(6) not null,
	redo_flag    VARCHAR2(1) default '0' not null
);
-- Add comments to the table 
comment on table DATA_OP_LOG
  is '数据操作记录表';
-- Add comments to the columns 
comment on column DATA_OP_LOG.id
  is '主键id';
comment on column DATA_OP_LOG.target_table
  is '操作目标表';
comment on column DATA_OP_LOG.reference_id
  is '操作目标记录id';
comment on column DATA_OP_LOG.reference_column
  is '操作目标记录主键字段';	
comment on column DATA_OP_LOG.op_type
  is '操作类型：0 删除；1 新增；2 修改';
comment on column DATA_OP_LOG.op_time
  is '操作时间';
comment on column DATA_OP_LOG.redo_flag
  is '重做标识：0 未重做；1 已重做';

• 创建函数createTriggerSql

create or replace function createTriggerSql(v_tableName  in varchar2,
                                              v_columnName in varchar2)
  return varchar2 as
  v_sql varchar2(4000);
begin
  v_sql:= 'create or replace trigger ' || v_tableName ||'_Trigger before insert or update or delete on ' || v_tableName ||' for each row
	 declare ids varchar2(128); nums  number:=0; begin ids:= '''';
	 case when INSERTING then insert into data_op_log(ID,TARGET_TABLE,REFERENCE_COLUMN,REFERENCE_ID,op_type,op_time) values(SYS_GUID(),''' || v_tableName || ''',''' || v_columnName || ''',:NEW.'||v_columnName||',''1'',sysdate);
when UPDATING then select count(1) into nums from  data_op_log dol  where  dol.target_table = ''' || v_tableName || '''  and dol.reference_id=:old.'||v_columnName||' and dol.reference_column=''' || v_columnName || ''' and dol.op_type=''2'';
					  if(nums > 0) then	select dol.id into ids from  data_op_log dol  where  dol.target_table = ''' || v_tableName || '''  and dol.reference_id=:old.'||v_columnName||' and dol.reference_column=''' || v_columnName || ''' and dol.op_type=''2''; update DATA_OP_LOG t set t.op_time=sysdate,t.redo_flag=''0'' where t.id=ids;
						else insert into data_op_log(ID,TARGET_TABLE,REFERENCE_COLUMN,REFERENCE_ID,op_type,op_time) values(SYS_GUID(),''' || v_tableName || ''',''' || v_columnName || ''',:NEW.'||v_columnName||',''2'',sysdate);end if;
when DELETING then insert into data_op_log(ID,TARGET_TABLE,REFERENCE_COLUMN,REFERENCE_ID,op_type,op_time) values(SYS_GUID(),''' || v_tableName || ''',''' || v_columnName || ''',:NEW.'||v_columnName||',''0'',sysdate);
   end case;
  end;';
  return v_sql;
end;

• 创建生成触发器的存储过程createTrigger

create or replace procedure createTrigger Authid Current_User is
  str varchar2(4000);                                          --执行sql
	v_error_code         VARCHAR2(10);                           --错误码
  v_description        VARCHAR2(160);                          --错误信息
begin
  for v in (SELECT ut.TABLE_NAME,cu.COLUMN_NAME
              FROM user_tables ut left join user_cons_columns cu on ut.TABLE_NAME = cu.TABLE_NAME
             where exists (select 1
                      from user_constraints au
                     where ut.TABLE_NAME = au.table_name
                       and au.constraint_type = 'P'
											 and au.CONSTRAINT_NAME = cu.CONSTRAINT_NAME)
											 and not regexp_like(ut.table_name, '^TEMPLATE_', 'i')
											 and not regexp_like(ut.table_name, '^QRTZ_', 'i')) loop
    begin
      str:=createTriggerSql(v.TABLE_NAME,v.COLUMN_NAME);
      --dbms_output.put_line('sql:' || str);
      execute immediate str;
exception
        when others then
					v_error_code   := SQLCODE;
          v_description   := substr(sqlerrm, 1, 160);
          ROLLBACK;
          dbms_output.put_line('sql:' || str || '执行异常错误码：' || v_error_code || '; 错误信息：' || v_description);
    end;
  end loop;
  -- commit;
end createTrigger;

• 创建重做日志的函数createRedoSql

  --创建重做sql脚本
  create or replace function createRedoSql(fromUser  in varchar2,toUser  in varchar2,tableName in varchar2,columnName  in varchar2,referenceId  in varchar2,opType  in varchar2)
    return varchar2 as
    v_sql varchar2(4000);
  begin
  	--判断操作类型
  	if('0'=opType) then 
  	v_sql:='delete from '||toUser||'.'||tableName||' where '||columnName||' = '''||referenceId||''';';
  	else if ('1'=opType) then 
  	v_sql:='insert into '||toUser||'.'||tableName||' select * from '||fromUser||'.'||tableName||''';';
  	else v_sql:='delete from '||toUser||'.'||tableName||' where '||columnName||' = '''||referenceId||'''; insert into '||toUser||'.'||tableName||' select * from '||fromUser||'.'||tableName||''';';
  	end if;
    return v_sql;
  end createRedoSql;
  

   

• 创建重做日志的存储过程redoWithLog

  --创建重做存储过程 
  create or replace procedure redoWithLog(fromUser varchar2,toUser varchar2) Authid Current_User is
    str varchar2(4000);                                          --执行sql
    v_error_code         VARCHAR2(10);                           --错误码
    v_description        VARCHAR2(160);                          --错误信息
  begin
    for v in (SELECT *
                FROM data_op_log dol 
               where dol.redo_flag='0' order by dol.op_time asc) loop
      begin
        str:=createRedoSql(fromUser,toUser,v.TABLE_NAME,v.COLUMN_NAME,v.reference_id,v.op_type);
        --dbms_output.put_line('sql:' || str);
        execute immediate str;
  			update data_op_log l set l.redo_flag='1' where l.id = v.id;
        exception
          when others then
            v_error_code   := SQLCODE;
            v_description   := substr(sqlerrm, 1, 160);
            ROLLBACK;
            dbms_output.put_line('sql:' || str || '执行异常错误码：' || v_error_code || '; 错误信息：' || v_description);
      end;
    end loop;
    commit;
  end redoWithLog;