PLPGSQL级联触发器？

小编典典

PLPGSQL级联触发器？

sql

我试图创建一个触发器，以便每当我添加一条新记录时，它都会在同一张表中添加另一条记录。会话字段将仅采用1到4之间的值。因此，当我在会话中添加1时，我希望它添加另一条记录，但会话3被阻止。但是问题在于，这会导致级联触发器，并且一次又一次地插入自身，因为触发器是在插入时触发的。

例如，我有一个简单的表：

CREATE TABLE example
(
id      SERIAL PRIMARY KEY
,name   VARCHAR(100) NOT NULL
,session   INTEGER
,status VARCHAR(100)
);

我的触发函数是：

CREATE OR REPLACE FUNCTION add_block() RETURNS TRIGGER AS $$

BEGIN

INSERT INTO example VALUES (NEW.id + 1, NEW.name, NEW.session+2, 'blocked');

RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';

触发是：

CREATE TRIGGER add_block
AFTER INSERT OR UPDATE
ON example
FOR EACH ROW
EXECUTE PROCEDURE add_block();

我收到错误消息：

SQL statement "INSERT INTO example VALUES ( $1 +1,  $2 ,  $3 + 2, $4)"
PL/pgSQL function "add_block" line 37 at SQL statement

这个错误重复了很多次，以至于我看不到顶部。

我该如何解决？

编辑：

CREATE TABLE block_rules
(
id      SERIAL PRIMARY KEY
,session   INTEGER
,block_session   INTEGER
);

该表包含阻止规则。因此，如果将新记录插入到具有会话1的示例表中，则它会通过在上面的同一张（示例）表（而非block_rules）中插入具有阻塞状态的新记录来相应地阻塞会话3。会话2相同，但会阻止会话4。

block_rules表保存阻止会话的规则（或模式）。它拥有

id | session | block_session
------------------------------
1  | 1       | 3
2  | 2       | 4
3  | 3       | 2

我如何将其放在触发器的WHEN语句中，以及下面的Erwin Branstetter的答案？

谢谢

阅读 192

2021-05-16

共1个答案

小编典典

已编辑问题的新答案

此触发功能根据表中的信息添加被阻止的会话block_rules。

~~我假设这些表是通过链接链接的id-信息在问题中丢失了。~~
我现在假设阻止规则是所有会话的通用规则，并通过链接session。仅针对非阻塞会话调用触发器，并插入匹配的阻塞会话。

触发功能：

CREATE OR REPLACE FUNCTION add_block()
  RETURNS TRIGGER AS
$BODY$
BEGIN
    INSERT INTO example (name, session, status)
    VALUES (NEW.name
           ,(SELECT block_session
             FROM   block_rules
             WHERE  session = NEW.session)
           ,'blocked');

    RETURN NULL;
END;
$BODY$ LANGUAGE plpgsql;

扳机：

CREATE TRIGGER add_block
AFTER INSERT -- OR UPDATE
ON example
FOR EACH ROW
WHEN (NEW.status IS DISTINCT FROM 'blocked')
EXECUTE PROCEDURE add_block();

回答原始问题

仍有改进的空间。考虑以下设置：

CREATE OR REPLACE FUNCTION add_block()
  RETURNS TRIGGER AS
$BODY$
BEGIN

INSERT INTO example (name, session, status)
VALUES (NEW.name, NEW.session + 2, 'blocked');

RETURN NULL;
END;
$BODY$ LANGUAGE plpgsql;


CREATE TRIGGER add_block
AFTER INSERT -- OR UPDATE
ON example
FOR EACH ROW
WHEN (NEW.session < 3)
-- WHEN (status IS DISTINCT FROM 'blocked') -- alternative guess at filter
EXECUTE PROCEDURE add_block();

要点：

对于PostgreSQL 9.0或更高版本， 您可以在触发器定义中使用 WHEN条件 。这将是最有效的。对于较旧的版本，您在触发函数中使用相同的条件。
有 没有必要添加一列 ，如果你能定义的标准来辨别自动插入的行。您没有告诉，因此session > 2在我的示例中我假设只有自动插入的行。我添加了替代WHEN条件status = 'blocked'作为评论。
您应该始终提供INSERT的 列列表 。如果不这样做，以后对该表进行更改可能会产生意想不到的副作用！
千万不能插NEW.id + 1在触发手动。这不会 增加序列 ，而下一个序列INSERT将因重复的键冲突而失败。
id是serial专栏，所以什么也不要做。nextval()序列中的默认值将自动插入。
您的描述仅提及INSERT，但是您有一个触发器AFTER INSERT OR UPDATE。我剪掉了UPDATE一部分。
关键字plpgsql不必加引号。

2021-05-16