PLPGSQL级联触发器? [英] PLPGSQL Cascading Triggers?

问题描述

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

I am trying to create a trigger, so that when ever I add a new record it adds another record in the same table. The session field will only take values between 1 and 4. So when I add a 1 in session I want it to add another record but with session 3 blocked. But the problem is that it leads to cascading triggers and it inserts itself again and again because the trigger is triggered when inserted.

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

I have for example a simple table:

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

此错误重复多次，以至于我看不到顶部.

This error repeats itself so many times that I can't see the top.

我该如何解决?

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

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

This table holds the block rules. So if a new record is inserted into the EXAMPLE table with session 1 then it blocks session 3 accordingly by inserting a new record with blocked status in the same (EXAMPLE) table above (not block_rules). Same for session 2 but it blocks session 4.

block_rules表保存阻止会话的规则(或模式).它拥有

The block_rules table holds the rules (or pattern) to block a session by. It holds

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

我该如何在触发条件的WHEN语句中加上下面的Erwin Branstetter的答案?

How would I put that in the WHEN statement of the trigger going with Erwin Branstetter's answer below?

谢谢

推荐答案

已编辑问题的新答案

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

New answer to edited question

This trigger function adds blocked sessions according to the information in table block_rules.

我假设表已通过id链接-问题中缺少信息.
我现在假设阻止规则是所有会话的通用规则，并按session链接.仅针对非阻塞会话调用该触发器，并插入匹配的阻塞会话.

I assume that the tables are linked by id - information is missing in the question.
I now assume that the block rules are general rules for all sessions alike and link by session. The trigger is only called for non-blocked sessions and inserts a matching blocked 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();

---

原始问题的答案

仍有改进的空间.考虑以下设置:

---

Answer to original question

There is still room for improvement. Consider this setup:

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或更高版本，您可以使用 何时条件 .这将是最有效的.对于较旧的版本，您可以在触发函数中使用相同的条件.

  Major points:

  • For PostgreSQL 9.0 or later you can use a WHEN condition in the trigger definition. This would be most efficient. For older versions you use the same condition inside the trigger function.

    无需添加列，前提是您可以定义条件来识别自动插入的行.您没有告诉，因此在我的示例中，我假设只有自动插入的行具有session > 2.我为status = 'blocked'添加了另一个WHEN条件作为注释.

    There is no need to add a column, if you can define criteria to discern auto-inserted rows. You did not tell, so I assume that only auto-inserted rows have session > 2 in my example. I added an alternative WHEN condition for status = 'blocked' as comment.

    您应该始终为INSERT提供一个列列表.如果不这样做，以后对该表进行的更改可能会产生意想不到的副作用！

    You should always provide a column list for INSERTs. If you don't, later changes to the table may have unexpected side effects!

    不要不在触发器中手动插入NEW.id + 1.这不会增加顺序，下一个INSERT将失败，并出现重复的键冲突.
    id是serial列，因此请勿执行任何操作.自动插入序列中的默认nextval().

    Do not insert NEW.id + 1 in the trigger manually. This won't increment the sequence and the next INSERT will fail with a duplicate key violation.
    id is a serial column, so don't do anything. The default nextval() from the sequence is inserted automatically.

    您的描述只提到了INSERT，但是您有一个触发器AFTER INSERT OR UPDATE.我切掉了UPDATE部分.

    Your description only mentions INSERT, yet you have a trigger AFTER INSERT OR UPDATE. I cut out the UPDATE part.

    不必引用关键字plpgsql.

    这篇关于PLPGSQL级联触发器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！