如果满足条件则阻止插入 [英] Prevent insert if condition is met

问题描述

我有一个像这样的表Content:

id |文字 |日期 |idUser → 用户 |内容类型

还有另一个表格Answer:

idAnswer → 内容 |idQuestion → 内容 |被接受

我想确保 Answer 的日期大于 Question 的日期.问题是带有 contentType = 'QUESTION' 的 Content.

我尝试使用以下触发器解决此问题，但是当我尝试插入 Answer 时出现错误:

<块引用>

错误:记录new"没有字段idanswer"上下文:SQL 语句 "SELECT (SELECT "Content".date FROM "Content" WHERE "Content".id = NEW.idAnswer) < (SELECT "Content".date FROM "Content" WHERE "Content".id = NEW.id问题)"IF 处的 PL/pgSQL 函数check_valid_date_answer"第 2 行

触发:

CREATE TRIGGER check_valid_answer插入答案"后对于每一行执行程序 check_valid_date_answer();

触发功能:

CREATE FUNCTION check_valid_date_answer() RETURNS 触发器语言 plpgsql作为 $$BEGINIF (SELECT "Content".date FROM "Content"WHERE "Content".id = NEW.idAnswer)<(从内容"中选择内容".日期WHERE "Content".id = NEW.idQuestion)然后RAISE NOTICE 'This Answer is an invalid date';万一;退货；结束;$$;

所以，我的问题是:我真的需要为此创建触发器吗?我看到我不能在 Answer 中使用 CHECK 因为我需要与另一个表的属性进行比较.有没有其他(更简单/更好)的方法来做到这一点?如果没有，为什么会出现错误，我该如何解决?

解决方案

您的基本方法是合理的.触发器是一个有效的解决方案.除了3个问题外，它应该可以工作:

1) 您的命名约定:

我们需要查看您的确切表定义才能确定，但​​证据就在那里.错误消息说:has no field "idanswer" - 小写.不说 "idAnswer" - CaMeL 案例.如果您在 Postgres 中创建了 CaMeL 案例标识符，那么在它们的余生中，您一定会在任何地方对它们进行双引号.

• PostgreSQL 列名是否区分大小写?

2) 中止违规插入

• 要么引发 EXCEPTION 而不是友好的 NOTICE 来实际中止整个交易.

• 或 RETURN NULL 而不是 RETURN NEW 以静默中止插入的行而不引发异常并且不回滚任何内容.

我会做第一个.这可能会修复手头的错误并起作用:

创建函数 trg_answer_insbef_check()返回触发 AS$func$开始IF (SELECT c.date FROM "Content" c WHERE c.id = NEW."idAnswer")<(SELECT c.date FROM "Content" c WHERE c.id = NEW."idQuestion") THENRAISE EXCEPTION '这个答案是一个无效的日期';万一;退货；结尾$func$ LANGUAGE plpgsql;

正确的解决方案是使用 合法的小写名称，完全避免此类问题.这包括您不幸的表名以及列名 date，这是一个 保留字在标准 SQL 中，不应用作标识符 - 即使 Postgres 允许.

3) 应该是 BEFORE 触发器

CREATE TRIGGER insbef_check之前插入答案"FOR EACH ROW EXECUTE PROCEDURE trg_answer_insbef_check();

您想在执行任何其他操作之前中止无效插入.

当然，您必须确保时间戳表 Content 无法更改，或者您需要更多触发器来确保满足您的条件.
Answer 中的 fk 列也是如此.

I have a table Content like this:

id | text | date | idUser → User | contentType 

And another table Answer:

idAnswer → Content | idQuestion → Content | isAccepted

I want to ensure that the Answer's date is bigger than the Question's date. A question is a Content with contentType = 'QUESTION'.

I tried to solve this with the following trigger, but when I try to insert an Answer there's an error:

ERROR:  record "new" has no field "idanswer"
CONTEXT:  SQL statement "SELECT (SELECT "Content".date FROM "Content" WHERE "Content".id = NEW.idAnswer) < (SELECT "Content".date FROM "Content" WHERE "Content".id = NEW.idQuestion)"
PL/pgSQL function "check_valid_date_answer" line 2 at IF

Trigger:

CREATE TRIGGER check_valid_answer 
AFTER INSERT ON "Answer"
FOR EACH ROW EXECUTE PROCEDURE check_valid_date_answer();

Trigger function:

CREATE FUNCTION check_valid_date_answer() RETURNS trigger
    LANGUAGE plpgsql
    AS $$BEGIN
  IF (SELECT "Content".date FROM "Content"
      WHERE "Content".id = NEW.idAnswer)
   < (SELECT "Content".date FROM "Content"
      WHERE "Content".id = NEW.idQuestion) 
  THEN
    RAISE NOTICE 'This Answer is an invalid date';
  END IF;
  RETURN NEW;
END;$$;

So, my question is: do I really need to create a trigger for this? I saw that I can't use a CHECK in Answer because I need to compare with an attribute of another table. Is there any other (easier/better) way to do this? If not, why the error and how can I solve it?

解决方案

Your basic approach is sound. The trigger is a valid solution. It should work except for 3 problems:

1) Your naming convention:

We would need to see your exact table definition to be sure, but the evidence is there. The error message says: has no field "idanswer" - lower case. Doesn't say "idAnswer" - CaMeL case. If you create CaMeL case identifiers in Postgres, you are bound to double-quote them everywhere for the rest of their life.

• Are PostgreSQL column names case-sensitive?

2) Abort violating insert

• Either raise an EXCEPTION instead of a friendly NOTICE to actually abort the whole transaction.

• Or RETURN NULL instead of RETURN NEW to just abort the inserted row silently without raising an exception and without rolling anything back.

I would do the first. This will probably fix the error at hand and work:

CREATE FUNCTION trg_answer_insbef_check()
  RETURNS trigger AS
$func$
BEGIN
   IF (SELECT c.date FROM "Content" c WHERE c.id = NEW."idAnswer")
    < (SELECT c.date FROM "Content" c WHERE c.id = NEW."idQuestion") THEN
      RAISE EXCEPTION 'This Answer is an invalid date';
   END IF;
   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

The proper solution is to use legal, lower case names exclusively and avoid such problems altogether. That includes your unfortunate table names as well as the column name date, which is a reserved word in standard SQL and should not be used as identifier - even if Postgres allows it.

3) Should be a BEFORE trigger

CREATE TRIGGER insbef_check
BEFORE INSERT ON "Answer"
FOR EACH ROW EXECUTE PROCEDURE trg_answer_insbef_check();

You want to abort invalid inserts before you do anything else.

Of course you will have to make sure that the timestamps table Content cannot be changed or you need more triggers to make sure your conditions are met.
The same goes for the fk columns in Answer.

这篇关于如果满足条件则阻止插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！