在更新表之前检查表字段的总和 [英] check sum of a table field before updating the table

问题描述

大家好。

在oracle数据库中，我有一个表 year_balance ，其中包含每年及其现金余额。所有余额的总和表示总金额。知道我们总金额的方法是总计所有年份的余额。年余的余额可以是正数，但总余额不能。

我想在表格上插入/更新/删除年份平衡到做一些事情，以确保总量仍然是积极的触发器无法帮助我因为oralce阻止你查询你正在触发的表，即在我的触发器内

我必须总结所有的余额获得总余额的年数

但实际上ai不能，因为U一定不能查询表格你正在触发

我所有的都是：new和：old表示只有受影响的行

a交易每次只影响一条记录

那么怎样做才能获得触发器内的总余额？



表year_balance

Hello everybody
Inside an oracle database, I have a table year_balance which contains each year and its cash balance.the sum of all the balances expresses the total amount.the way to know the total amount we have is to sum the balances of all years.year's balance can be positive but the total balance cannot.
I want when inserting/updating/deleting on the table year_balance to do something to ensure the total amount is still positive triggers cannot help me because oralce prevents you from querying the table you are making a trigger on i.e inside my trigger
I have to sum the balances of all of the years to get the total balance
but actually ai cannot because U must not query a table u are making a trigger on
all I have are :new and :old which expresses only the affected rows
a transaction affects only one record each time
so what to do to get the total balance inside the trigger ?

the table year_balance

year	balance
2010	1000000
2011	 200000
2012	-2000000

我的触发器

My trigger

CREATE or REPLACE TRIGGER positive_year_balance
BEFORE UPDATE
   ON year_balance
   FOR EACH ROW

DECLARE   
newCashBalance real := select nvl(sum(cash_balance), 0) from year_balance;
BEGIN
  newCashBalance := newCashBalance - nvl(:old.cash_balance, 0) + nvl(:new.cash_balance, 0);
  
  if (newCashBalance >= 0)  then
    :new.cash_balance := :old.cash_balance;                             
  end if;  
END;

错误

ORA-04091：表名字是变异的，触发/功能可能看不到它



提前感谢

the error
ORA-04091: table name is mutating, trigger/function may not see it

thanks in advance

推荐答案

使用存储proc而不是触发器。您将要更改的值作为参数传入，检查新的总和，如果没问题，请执行更新，否则请跳过更新。您也可以使用输出参数，让您的应用知道发生了什么并做出相应的响应。 http://www.techonthenet.com/oracle/procedures.php

Use a stored proc instead of a trigger. You pass in the values you want to change as parameters, you check your new sums and if okay, do your update, otherwise skip the update. You can have an output parameter, too, to let your app know what happened and respond accordingly. http://www.techonthenet.com/oracle/procedures.php

这篇关于在更新表之前检查表字段的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！