postgresql - 如果满足特定条件，如何自动更新表

Question

我想运行触发器，但收到一条奇怪的错误消息。

我正在使用 PostgreSQL。

我想运行这个更新:

UPDATE tablename
SET status=5
WHERE id=NEW.id 
AND status=4
AND pk!=NEW.pk;
RETURN NEW;

...每当有人试图将状态设置为 4 时。

所以基本上我希望每个“id”只有一次状态 4。为了确保这一点，我想将具有相同 ID 且状态 = 4 的所有其他状态设置为状态 = 5。

所以我把它放到一个函数中:

CREATE FUNCTION public.statusfunction()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF 
AS $BODY$

BEGIN
UPDATE tablename
SET status=5
WHERE id=NEW.id 
AND status=4
AND pk!=NEW.pk;
RETURN NEW;
END;

$BODY$;
;

并使用触发器执行此函数:

CREATE TRIGGER statustrigger
    BEFORE INSERT OR UPDATE 
    ON public.tablename
    FOR EACH ROW
    EXECUTE PROCEDURE public.statusfunction();

但是当我测试这个触发函数，并尝试将状态更新为 4 时，已经存在具有相同状态和 ID 的数据集，我收到此错误消息:

ERROR:  stack depth limit exceeded
HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.
CONTEXT:  SQL statement "UPDATE public.tablename 
SET status=5
WHERE id=new.id
AND status=4"
PL/pgSQL function statusfunction() line 3 at SQL statement

Answer 1

您可以将其包含在触发器函数体中:

IF pg_trigger_depth() > 1 THEN
   RETURN NEW;
END IF;

你的问题是触发器中的 UPDATE 再次触发了触发器，所以你得到了一个无休止的递归。

检查触发深度是打破无休止递归的简单方法。