如何使用 PostgreSQL 触发器来存储更改(SQL 语句和行更改) [英] How can I use a PostgreSQL triggers to store changes (SQL statements and row changes)

问题描述

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

Using PostgreSQL triggers, is it possible to record the changes that have happened to a table due to INSERT or UPDATE SQL statements and log them to a file for later execution.

这只是暂时使用，所以可以做一些快速而肮脏的事情.

This is only to be used temporally so just something quick and dirty would do.

推荐答案

来自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();

这篇关于如何使用 PostgreSQL 触发器来存储更改(SQL 语句和行更改)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！