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;
the error
ORA-04091: table name is mutating, trigger/function may not see it
thanks in advance