历史表的设计与创建

目录

前言

如何创建历史表和触发器

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

---

前言

生产中我们会为表创建历史表（或叫审计表 / 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
)；


CREATE OR REPLACE FUNCTION trg_fct_adt()
	RETURNS TRIGGER AS $BODY$
DECLARE
	v_audit_action CHAR(1);
BEGIN
	v_audit_action := 'I';
	IF TG_OP = 'INSERT' THEN
		v_audit_action := 'I';
	ELSE
		v_audit_action := 'U';
	END IF;
	IF TG_OP = 'DELETE' THEN
		v_audit_action := 'D';
		EXECUTE 'insert into ' || current_schema || '.' || TG_TABLE_NAME || '_adt' ||
				' select $1 audit_action,now() audit_time, $2.*'
				USING v_audit_action, OLD;
		RETURN OLD;
	ELSE
		EXECUTE 'insert into ' || current_schema || '.' || TG_TABLE_NAME || '_adt' ||
				' select $1 audit_action,now() audit_time, $2.*'
				USING v_audit_action, NEW;
	END IF;
	RETURN NEW;
END
$BODY$ LANGUAGE 'plpgsql';


DROP TRIGGER IF EXISTS trg_fct_profile_adt ON profile;
CREATE TRIGGER trg_fct_profile_adt
    AFTER INSERT OR DELETE OR UPDATE 
    ON profile
    FOR EACH ROW
    EXECUTE FUNCTION trg_fct_adt();

运行上面这段SQL创建成功后，可以测试一下增删改profile表，如：

insert into profile(ID, NAME, LAST_MODIFIED, LAST_MODIFIED_BY, TXN_ID) VALUES (1, 'Test Profile', now(), 'bingwei', 1001);
update profile set name='Test Profile Update 1', LAST_MODIFIED=now(), LAST_MODIFIED_BY='bingwei', TXN_ID=1002 where ID=1;
delete from profile where id=1;

然后查询profile_adt表我们能看到对应的增删改历史记录，证明触发器正常工作。

select * from profile_adt;

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

如果我们有很多个表需要创建历史表，每个表都要重新写一次历史表和触发器的DDL就会很费功夫。我写了一个函数，可以为指定的表创建历史表及对应的触发器，表名作为参数。函数如下：

CREATE OR REPLACE FUNCTION trg_fct_adt()
	RETURNS TRIGGER AS $BODY$
DECLARE
	v_audit_action CHAR(1);
BEGIN
	v_audit_action := 'I';
	IF TG_OP = 'INSERT' THEN
		v_audit_action := 'I';
	ELSE
		v_audit_action := 'U';
	END IF;
	IF TG_OP = 'DELETE' THEN
		v_audit_action := 'D';
		EXECUTE 'insert into ' || current_schema || '.' || TG_TABLE_NAME || '_adt' ||
				' select $1 audit_action,now() audit_time, $2.*'
				USING v_audit_action, OLD;
		RETURN OLD;
	ELSE
		EXECUTE 'insert into ' || current_schema || '.' || TG_TABLE_NAME || '_adt' ||
				' select $1 audit_action,now() audit_time, $2.*'
				USING v_audit_action, NEW;
	END IF;
	RETURN NEW;
END
$BODY$ LANGUAGE 'plpgsql';
	
	
CREATE OR REPLACE FUNCTION fnt_create_adt_table_and_triggers(i_tablename TEXT)
	RETURNS TEXT AS
$PROC$
DECLARE
	v_sql TEXT;
	v_audit_table_name TEXT;
BEGIN

	v_audit_table_name := i_tablename || '_adt';
	EXECUTE 'drop table if exists ' || v_audit_table_name;
	FOR v_sql IN
		WITH cols AS
			(
				select t.relname as table_name,
						c.attname as column_name,
						format_type(ty.oid, c.atttypmod) as column_type,
						na.nspname as table_schema,
						c.attnum as ordinal_position,
						c.attnotnull
				from pg_class t
					inner join pg_attribute c on t.oid = c.attrelid and c.attnum > 0
					inner join pg_type ty on ty.oid = c.atttypid
					inner join pg_namespace tn on ty.typnamespace = tn.oid
					inner join pg_class cl on c.attrelid = cl.oid
					inner join pg_namespace na on cl.relnamespace = na.oid
				where t.relname = i_tablename
				and t.relkind in ('r')
				and not c.attisdropped
				order by c.attnum
			)
		SELECT 'CREATE TABLE ' || quote_ident(table_schema)
			|| '.' || v_audit_table_name || ' (audit_action char(1) not null, audit_time TIMESTAMP not null,'
			|| string_agg(quote_ident(column_name)
			|| ' ' || column_type ||
				CASE
					WHEN attnotnull
						THEN ' not null '
					ELSE '' END, ', '
				ORDER BY ordinal_position)
			|| ')' AS result
		FROM cols
		GROUP BY table_schema, table_name
		LOOP
			BEGIN
				EXECUTE v_sql;
				EXECUTE 'DROP TRIGGER IF EXISTS trg_fct_' || v_audit_table_name || ' ON ' || i_tablename || ' CASCADE';
				EXECUTE 'CREATE TRIGGER trg_fct_' || v_audit_table_name || ' AFTER INSERT OR UPDATE OR DELETE ON ' ||
						i_tablename || ' FOR EACH ROW EXECUTE PROCEDURE trg_fct_adt()';
			EXCEPTION
				WHEN OTHERS
					THEN
						RAISE NOTICE 'sql create exception : %, %', v_sql, SQLERRM;
						RETURN v_audit_table_name || ' table and trg_fct_' || v_audit_table_name || ' trigger creation error';
			END;
		END LOOP;
	RETURN v_audit_table_name || ' table and trg_fct_' || v_audit_table_name || ' trigger created'; 	
END;
$PROC$ LANGUAGE plpgsql
	VOLATILE;	

使用方法：调用函数，传入要创建历史表的表明，如profile，函数就会创建历史表profile_adt和触发器trg_fct_profile_adt

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
);
select fnt_create_adt_table_and_triggers('profile');

运行上面的DDL，会发现对应的profile_adt和trg_fct_profile_adt都创建好了。

测试一下，增删改profile表，会发现profile_adt表有对应的历史记录，效果跟单独创建profile_adt和trg_fct_profile_adt是一样的。

insert into profile(ID, NAME, LAST_MODIFIED, LAST_MODIFIED_BY, TXN_ID) VALUES (1, 'Test Profile', now(), 'bingwei', 1001);
update profile set name='Test Profile Update 1', LAST_MODIFIED=now(), LAST_MODIFIED_BY='bingwei', TXN_ID=1002 where ID=1;
delete from profile where id=1;

select * from profile_adt;