创建历史表

最新推荐文章于 2023-07-13 22:36:30 发布

边缘元素

最新推荐文章于 2023-07-13 22:36:30 发布

阅读量1.5k

点赞数 1

分类专栏： oracle

本文链接：https://blog.csdn.net/john2522/article/details/10451177

版权

oracle 专栏收录该内容

38 篇文章 0 订阅

订阅专栏

对于某个业务表逻辑比较复杂，修改数据频繁，这时候为了方便开发定位问题，常常对该表的每次修改的数据都保持。我们可以使用另外一张表来记录信息，一般开发人员会称呼这些表为历史表。

下面举例，私人图书馆的-图书预约记录历史表（这只是举例，因为-图书预约记录不需要历史表的,哈哈）

--图书预约记录历史表【BOOKING_RECORD_HISTORY】

create table SYS.BOOKING_RECORD_HISTORY
(     
  ID_BOOKING_RECORD_HISTORY         VARCHAR2(32) NOT NULL,
  ID_BOOKING_RECORD                            VARCHAR2(32),
  CREATED_BY                                              VARCHAR2(100),
  DATE_CREATED                                        DATE,
  UPDATED_BY                                             VARCHAR2(100),
  DATE_UPDATED                                       DATE,
  DATE_UPDATED_MSEL                         TIMESTAMP(6)
);
  
-- Add comments to the table 
comment on table SYS.BOOKING_RRCORD_HISTORY is '历史表';
-- Add comments to the columns 
comment on column SYS.BOOKING_RRCORD_HISTORY.ID_BOOKING_RRCORD_HISTORY
  is '历史表主键';  
comment on column SYS.BOOKING_RRCORD_HISTORY.ID_BOOKING_RRCORD
  is 'BOOKING_RRCORD基础表主键';
comment on column SYS.BOOKING_RRCORD_HISTORY.CREATED_BY
  is '创建人';
comment on column SYS.BOOKING_RRCORD_HISTORY.DATE_CREATED
  is '创建时间';
comment on column SYS.BOOKING_RRCORD_HISTORY.UPDATED_BY
  is '创建人';
comment on column SYS.BOOKING_RRCORD_HISTORY.DATE_UPDATED
  is '更新时间';
comment on column SYS.BOOKING_RRCORD_HISTORY.DATE_UPDATED_MSEL
  is '修改时间精确到毫秒'; 
  
  -- CREATE INDEX
CREATE UNIQUE INDEX SYS.PK_BOOKING_RRCORD_HISTORY_ID on SYS.BOOKING_RRCORD_HISTORY (ID_BOOKING_RRCORD_HISTORY);
-- CREATE/RECREATE PRIMARY, UNIQUE AND FOREIGN KEY CONSTRAINTS 
ALTER TABLE SYS.BOOKING_RRCORD_HISTORY
  ADD CONSTRAINT PK_BOOKING_RRCORD_HISTORY_ID PRIMARY KEY (ID_BOOKING_RRCORD_HISTORY) USING INDEX PK_BOOKING_RRCORD_HISTORY_ID;

然后创建触发器，为了方便以后维护，可以加一个判断，当不需要记录历史表时，则跳过该过程。就像一个开挂一样来控制，比如，通过表booking_tr_control来控制，当表中查不到对应的触发器的记录时，则不记录到历史表。

delete from booking_tr_control hu where hu.trigger_name = 'BOOKING_RRCORD_HISTORY_AU' and hu.switch_for = 'BOOKING_RRCORD_HI_AU';
insert into booking_tr_control values('BOOKING_RRCORD_HISTORY_AU','BOOKING_RRCORD_HI_AU','1');

CREATE OR REPLACE TRIGGER MYTRG.BOOKING_RRCORD_HISTORY_AU
  AFTER INSERT OR UPDATE ON SYS.BOOKING_RRCORD
  for each row
declare
  v_sqlerrm       varchar2(800);
  v_sqlcode       varchar2(6);
  v_error_comment varchar2(300);
  v_trigger_user  varchar2(30);
  v_count         number;
begin
  select user into v_trigger_user from dual;
  select count(0)
    into v_count
    from booking_tr_control
   where trigger_name = 'BOOKING_RRCORD_HISTORY_AU'
     and switch_for = 'BOOKING_RRCORD_HI_AU'
     and status = '1';
  -- trigger 开关  
  if v_count > 0 then
    insert into BOOKING_RRCORD_HISTORY
      (ID_BOOKING_RRCORD_HISTORY,
       ID_BOOKING_RRCORD,
       CREATED_BY,
       DATE_CREATED,
       UPDATED_BY,
       DATE_UPDATED,
       DATE_UPDATED_MSEL)
    values
      (sys_guid(),
       :new.ID_BOOKING_RRCORD,
       :new.CREATED_BY,
       :new.DATE_CREATED,
       :new.UPDATED_BY,
       :new.DATE_UPDATED,
       systimestamp);
  end if;
exception
  when others then
    v_sqlcode := sqlcode;
    v_sqlerrm := substr(sqlerrm, 1, 200);
    insert into tr_error_log
      (error_no, --系统错误代码
       error_message, --系统错误信息
       trigger_name, --出错的trigger
       trigger_user, --出错的用户
       trigger_date, --出错的时间
       error_comment --出错详细信息
       )
    values
      (v_sqlcode,
       v_sqlerrm,
       'BOOKING_RRCORD_HI_AU',
       v_trigger_user,
       sysdate,
       v_error_comment);
end;
/