Oracle 使用触发器计算平均值
对于学校项目，我们被迫拥有冗余信息并通过使用触发器来更新它。我们有一个名为“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：表名正在发生变化，触发器/函数可能看不到它。我们正在尝试“自主交易”，但感觉我们正在偏离我们的触发点。
我们怎样才能让这个触发器发挥作用呢？
For a school project we are forced to have redundant information and update it by using triggers. We have a table called 'recipe_ratings' that contains a 'rating' (numbers 0-100). In our 'recipes' table we have a redundant row called 'rating' that contains the average rating for that specific recipe.
We tried to create an Oracle trigger like this:
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;
But this gives us: ORA-04091: table name is mutating, trigger/function may not see it. We are experimenting with 'autonomous transaction' but it feels like we're drifting away from our trigger.
How can we make this trigger work?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我希望教授不会引导您走上使用自治事务的道路，这除了使用无效的数据模型之外，这将是对自治事务的可怕滥用。
在现实世界中，为了让这种事情发挥作用，您需要
显然，这类事情很快就会变得非常麻烦，这就是为什么存储冗余数据如此成问题。
如果您只需处理插入，并且可以使用 INSERT ... VALUES 保证所有插入都是单行插入，则可以在查询中查询
RECIPE_RATINGS表。这在现实世界中行不通，但在课堂上可能就足够了。如果您不介意每次更新
RECIPE_RATINGS中的一行时重新计算每个菜谱的平均评分——这在实践中将是灾难性的，但可能会起作用在足够小的数据集上——您可以有一个 after 语句触发器，它对 RECIPE 表的每一行进行相关更新。I hope the professor is not leading you down the path of using autonomous transactions which would be a hideous misuse of autonomous transactions in addition to using an invalid data model.
In the real world, in order for this sort of thing to work, you would need
RECIPE_RATINGStableObviously, that sort of thing gets quite cumbersome quite quickly which is why storing redundant data is so problematic.
If you only had to handle inserts and you could guarantee that all inserts would be single-row inserts using the INSERT ... VALUES, you could query the
RECIPE_RATINGStable in your query. That doesn't work in the real world, but it may suffice in a classroom.If you don't mind re-computing the average rating for every recipe every time a single row in
RECIPE_RATINGSis updated-- something that would be catastrophic in practice but may work on a sufficiently small data set-- you could have an after statement trigger that does a correlated update on every row of theRECIPEtable.您的数据模型有多灵活？
您可以存储所有评级的总和加上评级的数量，而不是存储食谱的平均评级。
评级的插入触发器将采用值或新行来更新父配方行，以将评级添加到总数中，并将 1 添加到评级的数量/计数中。
更新触发器会将 :NEW 和 :OLD 值之间的差异添加到总数中（并且不更新计数）。
两个触发器都不必查询评级表上的其他行，从而防止变异表错误，并使其在具有多个并发用户的环境中使用更加安全。
查询（或视图或派生列）只需将总数除以计数即可确定平均值。
How flexible is your data model ?
Rather than the storing the average rating on the recipe, can you store the total of all the rating plus the number of ratings.
An insert trigger on ratings would take the values or the new row to update the parent recipe row to add the rating to the total and 1 to the number/count of ratings.
An update trigger would add the difference between the :NEW and :OLD values to the total (and not update the count).
Neither trigger has to query other rows on the ratings table preventing the mutating table error, and making it much safer to use in an environment with multiple concurrent users.
The query (or a view or a derived column) would determine the average simply by dividing the total by the count.
本文提供了一种避免这些错误的方法。
另一个想法 - “正常”触发器比 FOR EACH ROW 触发器更适合这里吗？如果一个语句中同一食谱有多个 Recipe_Rating 更新，否则您将多次计算平均值（因此会出现突变警告）。
This article gives one means of avoiding these errors.
Another thought - would a 'normal' trigger, rathen than a FOR EACH ROW trigger be more suitable here? If there are multiple recipe_rating updates for the same recipe in one statement you're calculating the average multiple times otherwise (hence the mutation warning).