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 | contentTypeAnd another table Answer:idAnswer → Content | idQuestion → Content | isAcceptedI 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: Trigger:CREATE TRIGGER check_valid_answerAFTER 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 insertEither 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 triggerCREATE TRIGGER insbef_checkBEFORE 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. 这篇关于如果满足条件则阻止插入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 上岸,阿里云!
07-25 04:17
查看更多