postgresql - 如何根据同一表中其他列的更改更新列值，而表中没有任何主键列

Question

我有一个名为“custom_manual_edit”的表，其中包含“name”、“builder”和“flag”列，其中没有带主键的列。当用户更新 builder 列中的任何更改时，我编写了一个触发器，并且该触发器将调用一个函数，该函数应将标记列值更新为 10，以获取更改构建器值的记录下面是我的触发器

CREATE TRIGGER builder_update_trigger_manual_custom_edits
AFTER UPDATE
ON edmonton.custom_manual_edit
FOR EACH ROW
WHEN (((old.builder)::text IS DISTINCT FROM (new.builder)::text))
EXECUTE PROCEDURE 
edmonton.automated_builder_update_trigger_manual_custom_edits();

和我的功能

  CREATE OR REPLACE FUNCTION 
edmonton.automated_builder_update_trigger_manual_custom_edits()
RETURNS trigger AS
$BODY$
DECLARE
e record;

BEGIN
IF NEW.builder <> OLD.builder THEN
    EXECUTE FORMAT('UPDATE edmonton.custom_manual_edit set builder_edit_flag = 10;
END IF;
RETURN NEW;

 END 
 $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

我知道这会将整个表标志列更新为 10，但如何更新构建器值已更改的记录的标志值。

Answer 1

请查看文档:36.1. Overview of Trigger Behavior

Trigger functions invoked by per-statement triggers should always return NULL. Trigger functions invoked by per-row triggers can return a table row (a value of type HeapTuple) to the calling executor, if they choose. A row-level trigger fired before an operation has the following choices:

• It can return NULL to skip the operation for the current row. This instructs the executor to not perform the row-level operation that invoked the trigger (the insertion, modification, or deletion of a particular table row).

• For row-level INSERT and UPDATE triggers only, the returned row becomes the row that will be inserted or will replace the row being updated. This allows the trigger function to modify the row being inserted or updated.

A row-level BEFORE trigger that does not intend to cause either of these behaviors must be careful to return as its result the same row that was passed in (that is, the NEW row for INSERT and UPDATE triggers, the OLD row for DELETE triggers).

根据以上内容你必须:

1. 将触发器声明为更新前，而不是更新后
2. 直接在新行中更改builder_edit_flag 列值，而不是触发 UPDATE 语句

---

CREATE TRIGGER builder_update_trigger_manual_custom_edits
BEFORE UPDATE
ON edmonton.custom_manual_edit
FOR EACH ROW
.....
.....

---

CREATE OR REPLACE FUNCTION 
edmonton.automated_builder_update_trigger_manual_custom_edits()
.....
.....

BEGIN
IF NEW.builder <> OLD.builder THEN
    NEW.builder_edit_flag = 10;
END IF;
RETURN NEW;
.....
.....