在触发器期间更新oracle中的同一行？ [英] Updating the same row in oracle during a trigger?

问题描述

简短的问题，因为我不知道如何搜索这个。我可以重新更新同一行吗？例如，我有一个字段存储付款的小计，并且鉴于我的业务约束，我可以更新该值。我可以更新只有触发器的同一行的总计吗？先谢谢！

Short question since I don't know how to search for this. Can I "re-update" the same row? For example, I have a field that stores the sub-total of a payment, and given my business constraints I can update that value. Can I update the total of the same row with just a trigger? Thank you beforehand!

我正在使用Oracle和PL / SQL。

By the way I'm using Oracle and PL/SQL.

业务规则：我有以下。有一个表，商店将支付数据，另一个表存储每月支付的费用，另一个存储可能的折扣。一个人将支付只能打折一次，而支付存储小计和总。所以，我在做什么是当折扣信息正在更新，提交后，更新总价值和费用的价值。

Business rules: I have the following. There's a table that stores will pays data, another table that stores the monthly fees to be paid, and another one that stores the possible discounts. One will pay can only be discounted once, and the will pay stores the subtotal and the total. So, what I'm doing is... "when the discount information is being updated, after it's committed, update the total value and the values of the fees".

推荐答案

你不能更新触发器触发的表，你会得到一个ORA-04091变异表错误。您可以使用：NEW 语法更新该行本身的值，只要它是一个'before'触发器即可。

You can't update the table your trigger is firing against, you'll get an ORA-04091 mutating table error. You can update values in the row itself, using the :NEW syntax, as long as it's a 'before' trigger.

我不清楚你对小计的意思，它听起来像你在表上有一个运行总字段;如果它基于同一个表上的其他记录（例如，对于同一个订单，您有多个记录，并且您希望插入的记录具有所有先前的记录的总和）。

I'm unclear what you mean about the subtotal though; it sounds like you have a running total field on the table; if thats based on other records on the same table (e.g. you have multiple records for the same order, and you want an inserted record to have the sum of all the previous ones). If that is the case then you can't do that either, as you'd hit the same ORA-04901.

如果你更新

这将有助于看到你的业务逻辑是什么，它如何适应插入/更新，以及什么你想要触发器做。通常有这样的东西，你真的想要使用一个包装程序的插入/更新，而不是一个触发器，但它目前有点模糊。

It would be helpful to see what your business logic is and how it fits in with the insert/update, and what you want the trigger to do. Often with something like this you really want to be using a wrapper procedure around the insert/update, rather than a trigger, but it's a bit vague at present.

小计以保持准确，我可能会避免尝试维护它，而是使用一个视图，它有一个分析函数为你计算它。根据我的经验，更麻烦。

For the subtotal to remain accurate, I'd probably avoid trying to maintain it all, and instead use a view which has an analytic function calculating it for you. Much less hassle, in my experience.

这篇关于在触发器期间更新oracle中的同一行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！