Postgresql 9.2触发器将更改记录记录到另一个表中 [英] Postgresql 9.2 trigger to log changes to data in another table

问题描述

我有几个表，想要记录对它们进行更改的时间，更改的内容以及更改的人。 PostgreSQL 9.2

I have several tables and want to log when changes are made to them, what the change was and who made the change. Postgresql 9.2

CREATE TABLE unitsref (
unitsrefid serial primary key,
units varchar,
unitname varchar,
inuse boolean,
systemuse varchar,
keynotes integer,
linkid integer
);

使用OLD的最佳做法。*与NEW不同。*？

Is the best practise to use OLD.* IS DISTINCT FROM NEW.* ?

CREATE TRIGGER log_unitsref
    AFTER UPDATE ON unitsref
    FOR EACH ROW
    WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE PROCEDURE log_unitsref();

我只对以下三个字段真正感兴趣：

I am only really interested in the three fields:

units varchar,
unitname varchar,
inuse boolean,

我想将这些更改记录在具有以下字段的表事件日志中：

I want to record these changes in a table eventlog with the fields:

recordtype varchar,
recordkey varchar,
changetype varchar,
personid integer,
changedate date,
changetime time,
changefrom varchar,
changeto varchar,

编写函数来执行此操作的最佳语法是什么？
进行中Openedge我会写

What is the best syntax to write a function to do this? In Progress Openedge I would write

create  EventLog.
assign  EventLog.PersonId    = glb-Personid
        EventLog.RecordType  = "UnitsRef"
        EventLog.RecordKey   = UnitsRef.Units
        EventLog.ChangeType  = "Create"
        EventLog.changeFrom  = ""
        EventLog.changeTo    = ""
        EventLog.changeDate  = today
        EventLog.changeTime  = time

但是我不知道Postgresql中最好的方法

but I don`t know the best method in Postgresql

推荐答案

我只对这三个字段真正感兴趣

I am only really interested in the three fields

然后在更改后仅调用触发器应该更有效这些字段：

Then it should be more efficient to only call the trigger after changes to these fields:

CREATE TRIGGER log_unitsref
AFTER UPDATE OF units, unitname, inuse
ON unitsref
FOR EACH ROW
WHEN (OLD.units, OLD.unitname, OLD.inuse) IS DISTINCT FROM
     (NEW.units, NEW.unitname, NEW.inuse)
EXECUTE PROCEDURE log_unitsref();

我引用 手册上的创建触发器 ：

更新 ...

仅当列出的列中至少有一个是
作为UPDATE命令的目标时，才会触发触发器。

The trigger will only fire if at least one of the listed columns is mentioned as a target of the UPDATE command.

何时 ...

一个布尔表达式，确定是否实际执行触发功能。

A Boolean expression that determines whether the trigger function will actually be executed.

这两个元素密切相关，但既不互斥也不冗余。

Note that these two elements are closely related but neither mutually exclusive nor redundant.

• 如果不触发触发器，则便宜得多

• It is much cheaper not to fire the trigger at all, if no column of interest is involved.

如果未实际更改任何感兴趣的列，则不执行触发函数要便宜得多。

It is much cheaper not to execute the trigger function if no column of interest was actually altered.

相关答案此处或此处。 ..

这篇关于Postgresql 9.2触发器将更改记录记录到另一个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！