Postgres-轨道更改

Question

我想跟踪表中的历史记录更改，因为我创建了一个历史表，现在正在保存旧值和新值，但是我想从常规表中保存FK中的一列。

这是我的历史表：

CREATE TABLE IF NOT EXISTS sales.history (
    history_id integer PRIMARY KEY NOT NULL,
    mytable_id integer,
    table_name varchar(30),
    new_val json,
    old_val json,
    operation varchar(10),
    CONSTRAINT "fk sales.mytable_id to history.mytable_id" FOREIGN KEY (mytable_id) REFERENCES sales.sales (mytable_id)
) TABLESPACE pg_default;

我创建​​了一个跟踪更新，插入和删除

CREATE TRIGGER t_history BEFORE INSERT OR UPDATE OR DELETE ON sales.sales
 
        FOR EACH ROW EXECUTE PROCEDURE core.func_store_history_changes();

触发器

CREATE OR REPLACE FUNCTION core.func_store_history_changes() RETURNS trigger AS $$ 
        BEGIN 
                IF      TG_OP = 'INSERT'
                THEN
                        INSERT INTO sales.history (tabname, schemaname, operation, new_val)
                                VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(NEW));
                        RETURN NEW;
                ELSIF   TG_OP = 'UPDATE'
                THEN 
                        INSERT INTO sales.history (tabname, schemaname, operation, new_val, old_val)
 
                                VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP,
 
                                        row_to_json(NEW), row_to_json(OLD));
                        RETURN NEW;
                ELSIF   TG_OP = 'DELETE'
                THEN
                        INSERT INTO sales.history (tabname, schemaname, operation, old_val)
                                VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(OLD));
                        RETURN OLD;
                END IF;
        END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;

的 我的历史表？

问候

Answer 1

对于您的特定问题，我想您可以使用new.mytable_id 并在插入之后使用触发器 ...

以下是一个例子，我只写了第一个if（插入）。

CREATE TABLE IF NOT EXISTS sales.history (
    history_id serial PRIMARY KEY NOT NULL,
    mytable_id integer,
    table_name varchar(30),
    new_val json,
    old_val json,
    operation varchar(10),
    CONSTRAINT "fk sales.mytable_id to history.mytable_id" FOREIGN KEY (mytable_id) REFERENCES sales.sales (mytable_id)
) TABLESPACE pg_default;


CREATE TRIGGER t_history AFTER INSERT OR UPDATE OR DELETE ON sales.sales
 
        FOR EACH ROW EXECUTE PROCEDURE core.func_store_history_changes();


CREATE OR REPLACE FUNCTION core.func_store_history_changes() RETURNS trigger AS $ 
        BEGIN 
                IF      TG_OP = 'INSERT'
                THEN
                        INSERT INTO sales.history (table_name, operation,mytable_id, new_val)
                                VALUES (TG_RELNAME, TG_OP, NEW.mytable_id, row_to_json(NEW));
                        RETURN NEW;
                ELSIF   TG_OP = 'UPDATE'
                THEN 
                        INSERT INTO sales.history (tabname, schemaname, operation, new_val, old_val)
 
                                VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP,
 
                                        row_to_json(NEW), row_to_json(OLD));
                        RETURN NEW;
                ELSIF   TG_OP = 'DELETE'
                THEN
                        INSERT INTO sales.history (tabname, schemaname, operation, old_val)
                                VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(OLD));
                        RETURN OLD;
                END IF;
        END;
$ LANGUAGE 'plpgsql' SECURITY DEFINER;

---

结果：

# insert into sales.sales (value) values ('2.3');
INSERT 0 1
# select * from sales.sales;
 mytable_id | value 
------------+-------          
          5 |  2.30
(2 rows)

# select * from sales.history;
 history_id | mytable_id | table_name |            new_val            | old_val | operation 
------------+------------+------------+-------------------------------+---------+-----------          
          3 |          5 | sales      | {"mytable_id":5,"value":2.30} |         | INSERT
(2 rows)

---

但是，也许您可​​以尝试为任何表使用此通用触发器。

https://github.com/2ndquadrant/audit-trigger