在触发过程中不执行任何操作 [英] Do nothing in a trigger procedure

问题描述

尝试执行触发器时遇到麻烦.假设我们有2个表，我想将数据从表A 复制到表B ，但是每个表都有一个唯一约束.

I got a trouble when a try to execute a trigger. Let's suppose we have 2 tables and I want to copy data from table A to table B but each table got a unique constraint.

create table test1 (
 test_name varchar);

create unique index test1_uc on test1 USING btree (test_name);

create table test2 (
test_name2 varchar);

 create unique index test2_uc on test2 USING btree (test_name2);

CREATE OR REPLACE FUNCTION trig_test()
  RETURNS trigger AS
$$
BEGIN
  IF pg_trigger_depth() <> 1 THEN
    RETURN NEW;
END IF;
INSERT INTO test2(test_name2)
   VALUES(NEW.test_name2)
ON CONFLICT (test_name2) DO NOTHING;

RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_test
AFTER INSERT
ON test2
FOR EACH ROW
EXECUTE PROCEDURE trig_test();

insert into test2 values ('test');
insert into test2 values ('test'); //should do nothing ?

但是我得到这个错误:

ERROR:  duplicate key value violates unique constraint "test2_uc"
DETAIL:  Key (test_name2)=(test) already exists.

触发器有什么问题?

推荐答案

您的示例已损坏.源和目标在触发器中的INSERT中相同，这势必每次都引发唯一冲突(emem)(插入NULL时除外)-被ON CONFLICT (test_name2) DO NOTHING禁止，因此在触发器.

Your example is broken. Source and target are the same in your INSERT in the trigger, which is bound to raise a unique violation every time (except when inserting NULL) - suppressed by ON CONFLICT (test_name2) DO NOTHING, so nothing ever happens in the trigger.

您还忘记了原始INSERT中的唯一约束.见下文.

You also forget about the unique constraint in your original INSERT. See below.

INSERT INTO test2(test_name2)
   VALUES(NEW.test_name2)

...

CREATE TRIGGER trigger_test
AFTER INSERT
ON test2

从一个不太混乱的设置开始:

Start with a less confusing setup:

CREATE TABLE test1 (col1 text UNIQUE);
CREATE TABLE test2 (col2 text UNIQUE);

移动 pg_trigger_depth() 到触发器本身.因此，将插入到test1中的行复制到test2(而不是以其他方式)，这仅适用于触发深度的 first 级别:

And it's more efficient to move pg_trigger_depth() to the trigger itself. So this would work, copying rows inserted into test1 to test2 (and not the other way), only for the first level of trigger depth:

CREATE OR REPLACE FUNCTION trig_test()
  RETURNS trigger AS
$func$
BEGIN
   INSERT INTO test2(col2)             -- !!
   VALUES (NEW.col1)                   -- !!
   ON     CONFLICT (col2) DO NOTHING;  -- !!

   RETURN NULL;
END
$func$ LANGUAGE plpgsql;

我将其保留为AFTER触发器.也可以是BEFORE触发器，但是您需要 RETURN NEW;.

I kept it as AFTER trigger. Can be a BEFORE trigger as well, but there you'd need RETURN NEW;.

CREATE TRIGGER trigger_test
AFTER INSERT ON test1                  -- !!
FOR EACH ROW 
WHEN (pg_trigger_depth() < 1)          -- !!
EXECUTE PROCEDURE trig_test();

为什么(pg_trigger_depth() < 1)?

• 如何防止PostgreSQL触发器被另一个触发器触发?

注意 ，您以这种方式在test2中捕获了唯一违规(什么也没有发生)，但是test1中的唯一违例仍会引发异常，除非您有ON CONFLICT ... DO NOTHING还有.您的测试是一厢情愿的:

Note that you trap unique violations in test2 this way (nothing happens), but unique violations in test1 would still raise an exception unless you have ON CONFLICT ... DO NOTHING there as well. Your test is wishful thinking:

insert into test2 values ('test'); //should do nothing ?

必须是:

INSERT INTO test1 values ('test') ON CONFLICT (col1) DO NOTHING;

替代:使用CTE链接两个INSERT

如果您可以控制test1上的INSERT命令，则可以执行此操作，而不要使用触发器:

Alternative: Chain two INSERT with a CTE

If you have control over INSERT commands on test1, you can do this instead of the trigger:

WITH ins1 AS (
   INSERT INTO test1(col1)
   VALUES ('foo')                  -- your value goes here
   ON CONFLICT (col1) DO NOTHING
   RETURNING *
   )
INSERT INTO test2(col2)
SELECT col1 FROM ins1
ON CONFLICT (col2) DO NOTHING;

相关:

• 在3个表中插入数据一次使用Postgres
• PostgreSQL多重插入...多列返回

• Insert data in 3 tables at a time using Postgres
• PostgreSQL multi INSERT...RETURNING with multiple columns

这篇关于在触发过程中不执行任何操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！