Запретить вставку, если условие выполнено

У меня есть стол Content как это:

id | text | date | idUser → User | contentType

И еще один стол Answer:

idAnswer → Content | idQuestion → Content | isAccepted

Я хочу убедиться, что Answerдата больше, чем Questionдата Вопрос Content с contentType = 'ВОПРОС'.

Я пытался решить эту проблему с помощью следующего триггера, но когда я пытаюсь вставить Answer есть ошибка:

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 в Answer потому что мне нужно сравнить с атрибутом другой таблицы. Есть ли другой (проще / лучше) способ сделать это? Если нет, то почему ошибка и как я могу ее решить?

Источник

2 ответа

Решение

Ваш основной подход - это звук. Триггер является допустимым решением. Должно работать кроме 3-х проблем:

1) Ваше соглашение об именах:

Нам нужно было бы увидеть ваше точное определение таблицы, чтобы быть уверенным, но доказательства есть. сообщение об ошибке гласит: has no field"idanswer" строчные буквы Не говорит "idAnswer" - CaMeL чехол. Если вы создаете идентификаторы случаев CaMeL в Postgres, вы обязаны заключать их в двойные кавычки всю оставшуюся жизнь.

2) Прервать нарушающую вставку

  • Либо поднять EXCEPTION вместо дружелюбного NOTICE на самом деле прервать всю транзакцию.

  • Или же RETURN NULL вместо RETURN NEW просто молча прервать вставленный ряд, не вызывая исключения и ничего не откатывая.

Я бы сделал первый. Это, вероятно, исправит ошибку и будет работать:

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, которое является зарезервированным словом в стандартном SQL и не должно использоваться в качестве идентификатора - даже если Postgres позволяет это.

3) Должно быть BEFORE спусковой крючок 

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

Вы хотите прервать недопустимые вставки, прежде чем делать что-либо еще.

Конечно, вы должны убедиться, что таблица меток времени Content не может быть изменено или вам нужно больше триггеров, чтобы убедиться, что ваши условия выполнены.
То же самое касается столбцов FK в Answer,

Источник

Я бы подошел к этому по-другому.

Рекомендация:

  • используйте триггер BEFORE INSERT, если вы хотите изменить данные перед их вставкой
  • используйте триггер AFTER INSERT, если вам нужно выполнить дополнительную работу
  • используйте предложение CHECK, если у вас есть дополнительные требования к согласованности данных.

Поэтому напишите функцию sql, которая проверяет условие, что одна дата должна быть раньше другой, и добавьте проверочное ограничение. Да, вы можете выбрать из других таблиц в вашей функции.

Я написал нечто подобное (сложная проверка) в ответ на этот вопрос на SO.

Источник
Другие вопросы по тегам