postgresql 如何使用 Postgres 触发器来存储更改
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1295795/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I use a Postgres Triggers to store changes
提问by jDempster
Is it possible using triggers in PostgreSQL to create update and insert sql statements of changes that have happened to a table and log them to a file for later execution.
是否可以在 PostgreSQL 中使用触发器来创建更新和插入表发生更改的 sql 语句，并将它们记录到文件中以供以后执行。
This is only to be used temporally so just something quick and dirty would do.
这只是暂时使用，所以只能做一些快速而肮脏的事情。
回答by nos
example of an audit trigger from https://www.postgresql.org/docs/current/static/plpgsql-trigger.html
来自https://www.postgresql.org/docs/current/static/plpgsql-trigger.html的审计触发器示例
CREATE TABLE emp (
empname text NOT NULL,
salary integer
);
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
stamp timestamp NOT NULL,
userid text NOT NULL,
empname text NOT NULL,
salary integer
);
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
BEGIN
--
-- Create a row in emp_audit to reflect the operation performed on emp,
-- make use of the special variable TG_OP to work out the operation.
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
RETURN NEW;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$emp_audit$ LANGUAGE plpgsql;
CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();
回答by David Claridge
Do you actually need the audit log of queries stored in a table? The easiest way to get a file with all the queries that have been executed is to use postgresql's built-in logging.
您真的需要存储在表中的查询审计日志吗？获取包含所有已执行查询的文件的最简单方法是使用 postgresql 的内置日志记录。
In your postgresql.conf (usually in the $PG_DATA dir), set the following options appropriately:
在您的 postgresql.conf（通常在 $PG_DATA 目录中）中，适当地设置以下选项：
log_directory '/path/to/log/dir'
log_filename = 'filename.log'
log_statement = 'mod'
That last option makes it log all the INSERT, UPDATE, DELETE, TRUNCATE, and COPY FROM statements.
最后一个选项使它记录所有的 INSERT、UPDATE、DELETE、TRUNCATE 和 COPY FROM 语句。
More details in the Postgres docs: http://www.postgresql.org/docs/current/static/runtime-config-logging.html
Postgres 文档中的更多详细信息：http: //www.postgresql.org/docs/current/static/runtime-config-logging.html
回答by David Claridge
The link below should point you in the right direction.
下面的链接应该为您指明正确的方向。
http://developer.postgresql.org/pgdocs/postgres/sql-createtrigger.html
http://developer.postgresql.org/pgdocs/postgres/sql-createtrigger.html
Depending on what you want to do, it probably is better to turn on logging.
根据您想要做什么，最好打开日志记录。
回答by AndreasPizsa
PostgreSQL Table Log by Andreas Scherbaum is a Postgresql extension that uses a trigger to log any INSERTs, UPDATEs and DELETEs on a specific table into another table.
Andreas Scherbaum 的 PostgreSQL 表日志是一个 Postgresql 扩展，它使用触发器将特定表上的任何插入、更新和删除记录到另一个表中。
The usage is easy: you create a second table which has the same format like your table you want to keep an eye on. Plus you need some additional columns to maintain the logged data.
用法很简单：您创建第二个表，该表的格式与您要关注的表相同。另外，您需要一些额外的列来维护记录的数据。
The second part of tablelog is able to restore the state of the original table or of a specific row for any time in the past.
tablelog 的第二部分能够恢复原始表或特定行过去任何时间的状态。
I haven't tried it myself, but it's supposedly working.
我自己没有尝试过，但据说它有效。
There's also slides from a talk about tablelog, but I can't post a proper link here due to some stackoverflow antispam weirdness :) (http :// andreas.scherbaum.la/writings/tablelog.pdf).
还有一些关于 tablelog 的幻灯片，但由于一些 stackoverflow 反垃圾邮件的奇怪之处，我无法在此处发布正确的链接:)（http://andreas.scherbaum.la/writings/tablelog.pdf）。
http://pgfoundry.org/projects/tablelog/
http://pgfoundry.org/projects/tablelog/
http:// andreas.scherbaum.la/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html
http://andreas.scherbaum.la/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html
