Click here to Skip to main content
15,891,788 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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

SQL
year	balance
2010	1000000
2011	 200000
2012	-2000000

My trigger
SQL
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
Posted
Updated 11-Jul-13 4:55am
v4
Comments
mgoad99 17-Jun-13 15:30pm    
I use SQL Server and not Oracle, but i would do all that in one transaction. If the insert/update/delete leaves the balance unacceptable, then rollback the transaction. Basically something like these steps:
Begin Tranaction
Do the insert/update/delete.
Check the balance.
If not a good value, then rollback the transaction.
End Transaction
mido_h_89 18-Jun-13 9:49am    
Thanks dude ...
But the problem is that I'm working on an old and complex code ...
so many places where the insert/update/delete is used , so I can't specify them all , so I want to but my condition on table level
Yuriy Loginov 11-Jul-13 10:57am    
have you considered adding a CHECK constraint on the year_balance field?
mido_h_89 21-May-14 5:22am    
check constraint doesn't work because it's restricted to one table only, my issue needs multiple tables
Yuriy Loginov 21-May-14 15:07pm    
You can call a function in a check constraint. The function can then reference multiple tables.

1 solution

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
 
Share this answer
 
Comments
mido_h_89 21-May-14 5:24am    
Using this way I need to add the stored procedure everytime an operation is done to the table. Thus again I need to find all those places where the modification occurs in my code which is unfeasible action. I need the checking to be done on the table level

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900