如何使用 PostgreSQL 触发器来存储更改（SQL 语句和行更改）

Question

使用 PostgreSQL 触发器，是否可以记录由于 INSERT 或 UPDATE SQL 语句而对表发生的更改，并将它们记录到文件中以供以后执行。

这只是暂时使用，所以只需一些快速而肮脏的东西就可以了。

Answer 1

审计触发器的示例
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();

Answer 2

您真的需要存储在表中的查询审核日志吗？ 获取包含所有已执行查询的文件的最简单方法是使用 postgresql 的内置日志记录。

在 postgresql.conf（通常在 $PG_DATA 目录中）中，适当地设置以下选项：

log_directory '/path/to/log/dir'
log_filename = 'filename.log'
log_statement = 'mod'

最后一个选项使其记录所有 INSERT、UPDATE、DELETE、TRUNCATE 和 COPY FROM 语句。

Postgres 文档中的更多详细信息：http://www.postgresql。 org/docs/current/static/runtime-config-logging.html

Answer 3

SQL 日志

如果您只对执行的语句感兴趣，那么您可以简单地激活 PostgreSQL 语句日志。

为此，打开 postgresql.conf 文件并设置以下配置属性：

log_statement = 'all' 

之后，您将看到记录在以下路径下的文件中的 SQL 语句：

$PG_DATA/pg_log/postgresql-YYYY-MM-DD_HHMMSS.log

但是，如果您想记录行-级别发生变化，那么就需要一个审计日志记录机制，可以使用触发器来实现，如下。

数据库表

假设我们有以下数据库表：

book_audit_log 将存储 book 表中发生的所有更改。

book_audit_log 的创建方式如下：

CREATE TABLE IF NOT EXISTS book_audit_log (
    book_id bigint NOT NULL,
    old_row_data jsonb,
    new_row_data jsonb,
    dml_type dml_type NOT NULL,
    dml_timestamp timestamp NOT NULL,
    dml_created_by varchar(255) NOT NULL,
    PRIMARY KEY (book_id, dml_type, dml_timestamp)
)

book_id 列存储由当前执行的 DML 语句插入、更新或删除的关联图书表记录的标识符。

old_row_data 和 new_row_data 列是 JSONB 类型，它们将捕获当前 INSERT、UPDATE 或 DELETE 语句执行前后图书行的状态。

dml_type 列存储当前执行的DML 语句的类型（例如INSERT、UPDATE 和DELETE）。 dml_type 类型是 PostgreSQL 枚举类型，其创建方式如下：

CREATE TYPE dml_type AS ENUM ('INSERT', 'UPDATE', 'DELETE')

dml_timestamp 列存储当前时间戳。

dml_created_by 列存储生成当前 INSERT、UPDATE 或 DELETE DML 语句的应用程序用户。

PostgreSQL 审计日志记录触发器

要捕获 book 表上的 INSERT、UPDATE 和 DELETE DML 语句，我们需要创建一个触发器函数，如下所示：

CREATE OR REPLACE FUNCTION book_audit_trigger_func()
RETURNS trigger AS $body$
BEGIN
   if (TG_OP = 'INSERT') then
       INSERT INTO book_audit_log (
           book_id,
           old_row_data,
           new_row_data,
           dml_type,
           dml_timestamp,
           dml_created_by
       )
       VALUES(
           NEW.id,
           null,
           to_jsonb(NEW),
           'INSERT',
           CURRENT_TIMESTAMP,
           current_setting('var.logged_user')
       );
             
       RETURN NEW;
   elsif (TG_OP = 'UPDATE') then
       INSERT INTO book_audit_log (
           book_id,
           old_row_data,
           new_row_data,
           dml_type,
           dml_timestamp,
           dml_created_by
       )
       VALUES(
           NEW.id,
           to_jsonb(OLD),
           to_jsonb(NEW),
           'UPDATE',
           CURRENT_TIMESTAMP,
           current_setting('var.logged_user')
       );
             
       RETURN NEW;
   elsif (TG_OP = 'DELETE') then
       INSERT INTO book_audit_log (
           book_id,
           old_row_data,
           new_row_data,
           dml_type,
           dml_timestamp,
           dml_created_by
       )
       VALUES(
           OLD.id,
           to_jsonb(OLD),
           null,
           'DELETE',
           CURRENT_TIMESTAMP,
           current_setting('var.logged_user')
       );
        
       RETURN OLD;
   end if;
     
END;
$body$
LANGUAGE plpgsql;

为了让 book_audit_trigger_func 函数在插入、更新或删除 book 表记录时，我们必须定义以下触发器：

CREATE TRIGGER book_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON book
FOR EACH ROW EXECUTE FUNCTION book_audit_trigger_func();

将 dml_created_by 列设置为 var.logged_user PostgreSQL 会话变量的值，该变量之前由应用程序使用当前登录的用户设置，如下所示：

SET LOCAL var.logged_user = 'Vlad Mihalcea'

测试时间

在 book 表上执行 INSERT 语句时：

INSERT INTO book (
    id,
    author, 
    price_in_cents, 
    publisher, 
    title
) 
VALUES (
    1,
    'Vlad Mihalcea', 
    3990, 
    'Amazon', 
    'High-Performance Java Persistence 1st edition'
)

我们可以看到在 book_audit_log 中插入了一条记录code> 捕获刚刚在 book 表上执行的 INSERT 语句：

| book_id | old_row_data | new_row_data                                                                                                                                  | dml_type | dml_timestamp              | dml_created_by |
|---------|--------------|-----------------------------------------------------------------------------------------------------------------------------------------------|----------|----------------------------|----------------|
| 1       |              | {"id": 1, "title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | INSERT   | 2020-08-25 13:19:57.073026 | Vlad Mihalcea  |

更新 book 表行时：

UPDATE book 
SET price_in_cents = 4499 
WHERE id = 1

我们可以看到一条新记录将被添加到book_audit_trigger 的 book_audit_log：

| book_id | old_row_data                                                                                                                                  | new_row_data                                                                                                                                  | dml_type | dml_timestamp              | dml_created_by |
|---------|-----------------------------------------------------------------------------------------------------------------------------------------------|-----------------------------------------------------------------------------------------------------------------------------------------------|----------|----------------------------|----------------|
| 1       |                                                                                                                                               | {"id": 1, "title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | INSERT   | 2020-08-25 13:19:57.073026 | Vlad Mihalcea  |
| 1       | {"id": 1, "title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | {"id": 1, "title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} | UPDATE   | 2020-08-25 13:21:15.006365 | Vlad Mihalcea  |

删除 book 表行时：

DELETE FROM book 
WHERE id = 1

一条新记录将添加到 book_audit_log book_audit_trigger：

| book_id | old_row_data                                                                                                                                  | new_row_data                                                                                                                                  | dml_type | dml_timestamp              | dml_created_by |
|---------|-----------------------------------------------------------------------------------------------------------------------------------------------|-----------------------------------------------------------------------------------------------------------------------------------------------|----------|----------------------------|----------------|
| 1       |                                                                                                                                               | {"id": 1, "title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | INSERT   | 2020-08-25 13:19:57.073026 | Vlad Mihalcea  |
| 1       | {"id": 1, "title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | {"id": 1, "title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} | UPDATE   | 2020-08-25 13:21:15.006365 | Vlad Mihalcea  |
| 1       | {"id": 1, "title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} |                                                                                                                                               | DELETE   | 2020-08-25 13:21:58.499881 | Vlad Mihalcea  |

Answer 4

下面的链接应该为您指明正确的方向。

https://www.postgresql.org/docs/current/sql-createtrigger。 html

根据您想要执行的操作，打开日志记录可能会更好。

Answer 5

Andreas Scherbaum 的 PostgreSQL 表日志是一个 Postgresql 扩展，它使用触发器将特定表上的任何 INSERT、UPDATE 和 DELETE 记录到另一个表中。

使用方法很简单：您创建第二个表，其格式与您要关注的表相同。 另外，您需要一些额外的列来维护记录的数据。

表日志的第二部分能够恢复原始表或过去任何时间的特定行的状态。

我自己没有尝试过，但据说它有效。

还有关于 tablelog 的演讲的幻灯片，但由于 stackoverflow 反垃圾邮件的一些奇怪之处，我无法在这里发布正确的链接:) (http://andreas.scherbaum.la/writings/tablelog.pdf)。

http://pgfoundry.org/projects/tablelog/

http://andreas.scherbaum.la /blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html