在触发函数中，如何获取正在更新的字段 [英] Within a trigger function, how to get which fields are being updated

问题描述

这可能吗?我有兴趣找出在 UPDATE 请求中指定了哪些列，而不管正在发送的新值可能是也可能不是已经存储在数据库中的值.

Is this possible? I'm interested in finding out which columns were specified in the UPDATE request regardless of the fact that the new value that is being sent may or may not be what is stored in the database already.

我想这样做的原因是因为我们有一个可以接收来自多个来源的更新的表.以前，我们没有记录更新的来源.现在该表存储了哪个源执行了最近的更新.我们可以更改一些来源以发送标识符，但这并不是所有事情的选择.所以我希望能够识别 UPDATE 请求何时没有标识符，以便我可以替换为默认值.

The reason I want to do this is because we have a table that can receive updates from multiple sources. Previously, we weren't recording which source the update originated from. Now the table stores which source has performed the most recent update. We can change some of the sources to send an identifier, but that isn't an option for everything. So I'd like to be able to recognize when an UPDATE request doesn't have an identifier so I can substitute in a default value.

推荐答案

如果源"没有发送标识符"，则该列将保持不变.然后，您无法检测当前的 UPDATE 是由与上一个相同的源还是由根本没有更改列的源完成的.换句话说:这不能正常工作.

If a "source" doesn't "send an identifier", the column will be unchanged. Then you cannot detect whether the current UPDATE was done by the same source as the last one or by a source that did not change the column at all. In other words: this does not work properly.

如果来源"可以通过任何 会话信息函数，你可以使用它.喜欢:

If the "source" is identifiable by any session information function, you can work with that. Like:

NEW.column = session_user;

每次更新都是无条件的.

Unconditionally for every update.

我找到了解决原始问题的方法.该列将在 any 更新中设置为默认值，其中列未更新(不在 的 SET 列表中更新).

I found a way how to solve the original problem. The column will be set to a default value in any update where the column is not updated (not in the SET list of the UPDATE).

关键元素是 每列触发器 在 PostgreSQL 9.0 中引入 - 使用 UPDATE OFcolumn_name 子句的特定列触发器.

Key element is a per-column trigger introduced in PostgreSQL 9.0 - a column-specific trigger using the UPDATE OFcolumn_name clause.

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

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

这是我发现的唯一简单的方法来区分列是否使用与旧值相同的新值更新，而不是根本没有更新.

That's the only simple way I found to distinguish whether a column was updated with a new value identical to the old, versus not updated at all.

一个可以还解析 current_query().但这似乎很棘手且不可靠.

One could also parse the text returned by current_query(). But that seems tricky and unreliable.

我假设列 col 定义了 NOT NULL.

I assume a column col defined NOT NULL.

第 1 步: 如果未更改，则将 col 设置为 NULL:

Step 1: Set col to NULL if unchanged:

CREATE OR REPLACE FUNCTION trg_tbl_upbef_step1()
  RETURNS trigger AS
$func$
BEGIN
   IF OLD.col = NEW.col THEN
      NEW.col := NULL;      -- "impossible" value
   END IF;

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

第 2 步:恢复为旧值.只有在值实际更新时才会触发触发器(见下文):

Step 2: Revert to old value. Trigger will only be fired, if the value was actually updated (see below):

CREATE OR REPLACE FUNCTION trg_tbl_upbef_step2()
  RETURNS trigger AS
$func$
BEGIN
   IF NEW.col IS NULL THEN
      NEW.col := OLD.col;
   END IF;

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

第 3 步:现在我们可以识别缺少的更新并改为设置默认值:

Step 3: Now we can identify the lacking update and set a default value instead:

CREATE OR REPLACE FUNCTION trg_tbl_upbef_step3()
  RETURNS trigger AS
$func$
BEGIN
   IF NEW.col IS NULL THEN
      NEW.col := 'default value';
   END IF;

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

触发器

Step 2 的触发器按列触发！

CREATE TRIGGER upbef_step1
  BEFORE UPDATE ON tbl
  FOR EACH ROW
  EXECUTE PROCEDURE trg_tbl_upbef_step1();

CREATE TRIGGER upbef_step2
  BEFORE UPDATE OF col ON tbl                -- key element!
  FOR EACH ROW
  EXECUTE PROCEDURE trg_tbl_upbef_step2();

CREATE TRIGGER upbef_step3
  BEFORE UPDATE ON tbl
  FOR EACH ROW
  EXECUTE PROCEDURE trg_tbl_upbef_step3();

触发器名称是相关的，因为它们是按字母顺序触发的(都是BEFORE UPDATE)！

Trigger names are relevant, because they are fired in alphabetical order (all being BEFORE UPDATE)!

该过程可以通过类似非列触发器"或任何其他方式来检查触发器中 UPDATE 的目标列表的方式来简化.但我看不出有什么办法.

The procedure could be simplified with something like "per-not-column triggers" or any other way to check the target-list of an UPDATE in a trigger. But I see no handle for this.

如果 col 可以是 NULL，则使用任何其他不可能"的中间值并在触发函数 1 中另外检查 NULL:

If col can be NULL, use any other "impossible" intermediate value and check for NULL additionally in trigger function 1:

IF OLD.col IS NOT DISTINCT FROM NEW.col THEN
    NEW.col := '#impossible_value#';
END IF;

相应地调整其余部分.

这篇关于在触发函数中，如何获取正在更新的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！