1、创建原表mytab
create table mytab(id int primary key,note text);
创建记录表mytab_log
create table mytab_log(seq bigserial primary key,
oprtype char(1),
oprtime timestamp,
old_id int,
new_id int,
old_note text,
new_note text);
2、创建规则
create rule rule_mytab_insert as on insert
to mytab
do also insert into mytab_log(oprtype,oprtime,new_id,new_note) values('i',now(),new.id,new.note);
create rule rule_mytab_update as on update
to mytab
do also insert into mytab_log(oprtype,oprtime,old_id,new_id,old_note,new_note) values('u',now(),old.id,new.id,old.note,new.note);
(更新数据时实现记录旧数据和新数据,类型为”u“)
create rule rule_mytab_delete as on delete
to mytab
do also insert into mytab_log(oprtype,oprtime,old_id,old_note) values('d',now(),old.id,old.note);
insert into mytab values(1,'abc');
insert into mytab values(2,'bac');
insert into mytab values(3,'cab');
tian=# select * from mytab;
id | note
----+------
1 | abc
2 | bac
3 | cab
(3 rows)
update mytab set note='ccc' where id=3;
delete from mytab where id=3;
tian=# select * from mytab;
id | note
----+------
1 | abc
2 | bac
(2 rows)
3、查看数据记录
tian=# select * from mytab_log;
seq | oprtype | oprtime | old_id | new_id | old_note | new_note
-----+---------+----------------------------+--------+--------+----------+----------
1 | i | 2017-02-25 15:19:20.932179 | | 1 | | abc
2 | i | 2017-02-25 15:19:26.350333 | | 2 | | bac
3 | i | 2017-02-25 15:19:31.822498 | | 3 | | cab
4 | u | 2017-02-25 15:19:50.297173 | 3 | 3 | cab | ccc
5 | d | 2017-02-25 15:19:56.557354 | 3 | | ccc |
(5 rows)
方式二:【改变更新记录数据的方式】
create table mytab2(id int primary key,note text);
create table mytab_log2(
seq bigserial primary key,
oprtype char(1),
oprtime timestamp,
id int,
note text);
create rule rule_mytab_insert2 as on insert
to mytab2
do also insert into mytab_log2(oprtype,oprtime,id,note) values('i',now(),new.id,new.note);
create rule rule_mytab_update2 as on update
to mytab2
do also (insert into mytab_log2(oprtype,oprtime,id, note) values('d',now(),old.id,old.note);
insert into mytab_log2(oprtype,oprtime,id, note) values('i',now(),new.id,new.note));
(更新数据时通过分解为删除和插入两部分实现记录旧数据和新数据,类型为”d“和”i“)
create rule rule_mytab_delete2 as on delete
to mytab2
do also insert into mytab_log2(oprtype,oprtime,id,note) values('d',now(),old.id,old.note);
tian=# insert into mytab2 values(1,'1111');
INSERT 0 1
tian=# insert into mytab2 values(2,'2222');
INSERT 0 1
tian=# insert into mytab2 values(3,'2222');
INSERT 0 1
tian=# select * from mytab_log2;
seq | oprtype | oprtime | id | note
-----+---------+----------------------------+----+------
1 | i | 2017-02-25 15:42:07.852126 | 1 | 1111
2 | i | 2017-02-25 15:42:15.638697 | 2 | 2222
3 | i | 2017-02-25 15:42:19.748022 | 3 | 2222
tian=# update mytab2 set note='3333' where id = 3;
UPDATE 1
tian=# delete from mytab2 where id =3;
DELETE 1
tian=# select * from mytab_log2;
seq | oprtype | oprtime | id | note
-----+---------+----------------------------+----+------
1 | i | 2017-02-25 15:42:07.852126 | 1 | 1111
2 | i | 2017-02-25 15:42:15.638697 | 2 | 2222
3 | i | 2017-02-25 15:42:19.748022 | 3 | 2222
4 | d | 2017-02-25 15:43:18.741909 | 3 | 2222
5 | i | 2017-02-25 15:43:18.741909 | 3 | 3333
6 | d | 2017-02-25 15:44:14.097896 | 3 | 3333
(6 rows)
日志紧凑了许多