Блог

Для школьного проекта мы вынуждены иметь избыточную информацию и обновлять ее с помощью триггеров. У нас есть таблица под названием «recipe_ratings», которая содержит «рейтинг» (цифры 0-100). В нашей таблице «рецепты» у нас есть избыточная строка под названием «рейтинг», которая содержит средний рейтинг для этого конкретного рецепта.

Мы попытались создать триггер Oracle, подобный этому:

 CREATE OR REPLACE TRIGGER trigger_rating 
AFTER UPDATE 
  ON recipe_ratings 
  FOR EACH ROW

DECLARE 
  average_rating NUMBER;

BEGIN
  SELECT ROUND(AVG(rating))
  INTO average_rating
  FROM recipe_ratings
  WHERE rid = :new.rid;

  UPDATE recipe SET rating = average_rating
  WHERE rid = :new.rid 

END;
  

Но это дает нам: ORA-04091: имя таблицы изменяется, триггер / функция может его не видеть. Мы экспериментируем с «автономной транзакцией», но кажется, что мы отдаляемся от нашего триггера.

Как мы можем заставить этот триггер работать?

Я надеюсь, что профессор не ведет вас по пути использования автономных транзакций, что было бы отвратительным злоупотреблением автономными транзакциями в дополнение к использованию недопустимой модели данных.

В реальном мире для того, чтобы такого рода вещи работали, вам потребуется

• Пакет с набором значений RID
• Триггер инструкции before, который инициализирует эту коллекцию
• Триггер на уровне строки, который вставляет значения :new.rid в коллекцию
• Триггер оператора after, который считывает коллекцию и выдает обновления в RECIPE_RATINGS таблице

Очевидно, что такого рода вещи довольно быстро становятся довольно громоздкими, поэтому хранение избыточных данных настолько проблематично.

Если бы вам нужно было обрабатывать только вставки, и вы могли бы гарантировать, что все вставки будут однорядными вставками с использованием INSERT … ЗНАЧЕНИЯ, вы могли бы запросить RECIPE_RATINGS таблицу в вашем запросе. Это не работает в реальном мире, но этого может быть достаточно в классе.

Если вы не возражаете повторно вычислять среднюю оценку для каждого рецепта каждый раз, когда обновляется одна строка в RECIPE_RATINGS — что на практике было бы катастрофой, но может сработать на достаточно небольшом наборе данных — у вас мог бы быть триггер оператора after, который выполняет коррелированное обновление для каждой строки RECIPE таблицы.

Насколько гибкой является ваша модель данных?

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

Триггер insert для оценок будет принимать значения или новую строку для обновления родительской строки рецепта, чтобы добавить оценку к итогу и 1 к количеству оценок.

Триггер обновления добавит разницу между :НОВЫМИ и:старыми значениями к общей сумме (и не обновит счетчик).

Ни один из триггеров не должен запрашивать другие строки в таблице рейтингов, предотвращая ошибку изменяющейся таблицы и делая ее намного безопаснее для использования в среде с несколькими одновременными пользователями.

Запрос (или представление, или производный столбец) определил бы среднее значение, просто разделив итоговое значение на количество.

В этой статье приводится один из способов избежать этих ошибок.

Еще одна мысль — не будет ли здесь более подходящим «обычный» триггер, а не триггер ДЛЯ КАЖДОЙ СТРОКИ? Если для одного и того же рецепта в одном операторе есть несколько обновлений recipe_rating, в противном случае вы вычисляете среднее значение несколько раз (отсюда предупреждение о мутации).