Postgresql - SQL - 对表中数据的插入、更新、删除进行跟踪

创建一个trigger，对表中的每一行数据的插入、更新、删除进行跟踪。

我们可以进行对数据的监控，对数据更改的保存，记录对所有数据的更改。

也可以依据trigger，记录到文件或表中，将数据应用到其他的数据库，就完成了逻辑复制。

总之，对表中的数据跟踪，是做这些事情的一个基础。

 

实验环境： pg 11 beta 3

在数据库中创建一个表，用来记录数据的更改。

 

-- 存储表名，操作类型，事务号

CREATE SEQUENCE data_trace_seqid_seq;

CREATE TABLE IF NOT EXISTS Data_Trace (

SeqId int8 not null default nextval('data_trace_seqid_seq'::regclass),

TableName Name NOT NULL,

Op character,

XID int8 NOT NULL,

PRIMARY KEY (SeqId)

);

 

-- 存储具体的操作的数据

-- 我们使用pg extension hstore 存储数据，hstore数据类型可以用来在单一的值中存储多个键值对。

CREATE EXTENTION hstore;

CREATE TABLE IF NOT EXISTS Data_TraceData (

SeqId int8 NOT NULL,

IsKey bool NOT NULL,

Data hstore,

PRIMARY KEY (SeqId, IsKey) ,

FOREIGN KEY (SeqId) REFERENCES Data_Trace (SeqId) ON UPDATE CASCADE ON DELETE CASCADE

);

 

-- Main Function， 将数据的改变写入到表中

CREATE OR REPLACE FUNCTION recordchange() RETURNS trigger AS $$

DECLARE

op text := CASE WHEN TG_OP = 'INSERT' THEN 'i' WHEN TG_OP = 'UPDATE' THEN 'u' WHEN TG_OP = 'DELETE' THEN 'd' ELSE 'ERROR' END;

trace_seq_id int8 := 0;

old_hstore hstore;

new_hstore hstore;

BEGIN

IF op IN ('i', 'u') THEN

new_hstore = hstore(NEW);

END IF;

 

IF op IN ('d', 'u') THEN

old_hstore = hstore(OLD);

END IF;

 

IF op = 'u' AND new_hstore = old_hstore THEN

RETURN NEW;

END IF;

trace_seq_id := nextval('data_trace_seqid_seq'::regclass);

 

INSERT INTO Data_Trace (seqid, TableName, Op, XID) VALUES (trace_seq_id, TG_RELID::regclass, op, txid_current());

IF op = 'i' THEN

INSERT INTO Data_TraceData (SeqId,IsKey,Data) VALUES(trace_seq_id, 'f', new_hstore);

ELSIF op = 'd' THEN

INSERT INTO Data_TraceData (SeqId,IsKey,Data)

VALUES(trace_seq_id, 't', dbmirror.key_for(TG_RELID::regclass, old_hstore));

ELSIF op = 'u' THEN

INSERT INTO Data_TraceData (SeqId,IsKey,Data) VALUES(trace_seq_id, 'f', new_hstore);

INSERT INTO Data_TraceData (SeqId,IsKey,Data)

VALUES(trace_seq_id, 't', dbmirror.key_for(TG_RELID::regclass, old_hstore));

ELSE

RAISE 'UNSUPPORTED OPERATION';

END IF;

 

RETURN NEW;

END;

$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

 

-- 创建一个测试表，并对表进行数据跟踪。

CREATE TABLE test1101(id int , col1 varchar(20), col2 varchar(20), col3 timestamp without time zone);

CREATE TRIGGER test1101_repl_trg AFTER INSERT OR UPDATE OR DELETE ON test1101 for each row execute procedure recordchange();

 

-- 插入数据

insert into test1101 values (1,'aa','bb',now());

insert into test1101 values (1,'aa','bb',now());

 

-- 查看跟踪表内的数据。

select * from data_trace;

seqid | tablename | op | xid

-------+-----------+----+-----

1 | test1101 | i | 794

2 | test1101 | i | 795

(2 rows)

 

select * from data_tracedata;

seqid | iskey | data

-------+-------+-----------------------------------------------------------------------------

1 | f | "id"=>"1", "col1"=>"aa", "col2"=>"bb", "col3"=>"2018-11-01 19:08:31.517192"

2 | f | "id"=>"1", "col1"=>"aa", "col2"=>"bb", "col3"=>"2018-11-01 19:08:32.77308"

(2 rows)

 

这个功能确实很有用的哦。可以随时记录数据的变更。