历史表的设计与创建

目录

前言

如何创建历史表和触发器

函数式创建历史表和触发器

---

前言

生产中我们会为表创建历史表（或叫审计表 / audit table / ADT）。历史表是为了记录数据的修改记录、修改类型（增、删、改）、修改时间等信息。

写入历史表常用的方法是通过触发器。举例说明，如下图所示，profile表是在用表，profile_adt是历史表，profile_adt应包含profile的所有字段，另外还需要两个字段（audit_action和audit_time）用于记录修改类型和时间。当profile表有insert / update / delete操作，就会触发trigger trg_fct_profile_adt写入profile_adt表。

 profile_adt的记录和profile的修改有如下关系：

对profile的修改类型	profile_adt.audit_action	profile_adt.audit_time	profile_adt的其他字段
Insert	I	当前时间	新增的profile
Update	U	当前时间	修改后的profile
Delete	D	当前时间	删除的profile

如何创建历史表和触发器

下面来尝试创建profile, profile_adt及其对应的触发器, DDL如下：

drop table if exists profile;
create table profile
(
	ID NUMERIC(22) NOT NULL primary key,
	NAME VARCHAR(255) NOT NULL,
	LAST_MODIFIED TIMESTAMP(6) NOT NULL,
	LAST_MODIFIED_BY VARCHAR(50) NOT NULL,
	TXN_ID NUMERIC(22) NOT NULL
);


DROP TABLE IF EXISTS profile_adt;
CREATE TABLE profile_adt
(
    audit_action character(1) NOT NULL,
    audit_time timestamp NOT NULL,
    id numeric(22) NOT NULL,
    NAME VARCHAR(255) NOT NULL,
	LAST_MODIFIED TIMESTAMP(6) NOT NULL,
	LAST_MODIFIED_BY VARCHAR(50) NOT NULL,
	TXN_ID NUMERIC(22) NOT NULL
)；