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

问题描述

我有一个表内容像这样：

id | text | date | idUser → User | contentType 

另一个表 p>



And another table Answer:

idAnswer → Content | idQuestion → Content | isAccepted

我想确保 Answer 的日期大于问题的日期。问题是内容与 contentType ='QUESTION'。

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

触发器：

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

触发器功能：

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;$$;

所以，我的问题是：我真的需要创建一个触发器吗？我看到我不能在回答中使用 CHECK ，因为我需要与另一个表的属性进行比较。有没有其他（更容易/更好）的方法来做到这一点？如果没有，为什么会出现错误，我该如何解决呢？

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?

推荐答案

触发器是有效的解决方案。除 3个问题除外：：

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

我们需要看到你确切的表定义，但是证据是存在的。错误消息说：没有字段 idanswer - 小写。不说 idAnswer - CaMeL的情况。

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 your life.

• 提出 EXCEPTION ，而不是友好的 code>实际中止整个交易。

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

或 RETURN NULL

我会做第一个。这可能会修复手头和工作的错误：

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;

正确的解决方案是使用合法的小写名称并避免这些问题。这包括您的不幸的表名称以及列名称 date ，这是一个保留字，并且不应该用作标识符 - 即使Postgres允许它。

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.

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

当然，您必须确保时间戳表 Content 不能更改，需要更多触发器以确保满足您的条件。

对于回答中的fk列也是如此。

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屋！