How to let a column have values reflected automatically, when value is inserted/updated/deleted to/from another column in the same table?

Question

I created 2 tables : INFORMATION AND FEED.

INFORMATION has 2 attributes : ID(Primary Key), TOT_AMOUNT.

FEED has 4 attributes : ID(Foreign key refer INFORMATION(ID)), S_AMOUNT, S_DATE, TOT_REM.

Now, I have to insert/update/delete values to/from TOT_REM, based on the insertion/removal/update of S_AMOUNT and TOT_AMOUNT.

The sample contents are :

INFORMATION Table
------------------
ID   |  TOT_AMOUNT
1    |    100
2    |    20
3    |    50
...

               FEED Table
----------------------------------------
ID   |   S_AMOUNT   |  S_DATE  | TOT_REM 
1    |     10       |10.10.2010|   90
1    |     10       |13.10.2010|   80
1    |     30       |17.10.2013|   50
1    |     10       |20.10.2016|   40
...

We need to automatically insert the value into TOT_REM attribute, based on the update/insert/delete operation performed on S_AMOUNT, with the help of TOT_AMOUNT & S_AMOUNT.

At any time, TOT_REM can't be less than 0. And, TOT_REM needs to be automatically inserted/removed/updated such that

TOT_REM for i(at a specific date) = (TOT_AMOUNT for ID=i) - 
                                    SUM(S_AMOUNT of all instances of ID=i,
                                    which is later than the S_DATE for ID=i); 

So, assuming if we delete the 2nd tuple(1,10,'13.10.2010',80), the reflected state of BR_FEED should be :

               FEED Table
----------------------------------------
ID   |   S_AMOUNT   |  S_DATE  | TOT_REM 
1    |     10       |10.10.2010|   90
1    |     30       |17.10.2013|   60
1    |     10       |20.10.2016|   50
...

I wrote a trigger, which fails showing

ORA-04091: table SSUMAN.FEED is mutating, trigger/function may not see it

The code for trigger is :

CREATE OR REPLACE TRIGGER BR_INSERT_TRB
AFTER DELETE OR INSERT OR UPDATE OF S_AMOUNT ON FEED
FOR EACH ROW
BEGIN

IF DELETING THEN 
UPDATE  FEED bf
SET     bf.TOT_REM = bf.S_AMOUNT + :OLD.S_AMOUNT;
END IF;

IF INSERTING THEN 
INSERT INTO FEED (TOT_REM) VALUES(
((SELECT TOT_AMOUNT FROM INFORMATION bi WHERE bi.ID=:NEW.ID) - 
(SELECT SUM(S_AMOUNT) FROM FEED bf where bf.ID=:NEW.ID) - 
:NEW.S_AMOUNT);
END IF;

IF UPDATING THEN 
UPDATE  FEED bf
SET     bf.TOT_REM = (SELECT TOT_AMOUNT FROM BR_INFORMATION bi WHERE bi.ID=bf.ID) - 
(SELECT SUM(S_AMOUNT) FROM FEED bf where bf.ID=:NEW.ID) - 
 :NEW.S_AMOUNT
WHERE   :NEW.ID IS NOT NULL;
END IF;   

END;

Questions :

1. Is this approach flawed? Can't I achieve what I want, with this way?[OPTIONAL]
2. Is there any scope of bringing view here? I am not able to think in that line! Probably, lack of experience...[OPTIONAL]
3. Any better approach, so that TOT_REM values can be automatically reflected?[COMPULSORY TO ANSWER]

Answer 1

I think it is better to create view. Look at this

Test data

create table feed(ID,S_AMOUNT,S_DATE) as  (
  SELECT 1,10, TO_DATE('10.10.2010','dd.mm.yyyy') FROM dual UNION all
  SELECT 1,10,TO_DATE('13.10.2010','dd.mm.yyyy') FROM dual UNION all
  SELECT 1,30,TO_DATE('17.10.2013','dd.mm.yyyy') FROM dual UNION all
  SELECT 2,10,TO_DATE('20.10.2016','dd.mm.yyyy') FROM dual)

create table INFORMATION (id, TOT_AMOUNT) as (
  SELECT 1,100 FROM DUAL UNION ALL
  SELECT 2,20 FROM DUAL UNION ALL
  SELECT 3,50 FROM DUAL)

Query

create or replace view result_feed as
  SELECT f.*,i.TOT_AMOUNT - NVL(SUM(S_AMOUNT) OVER(PARTITION BY f.ID ORDER BY f.S_DATE),0) AS tot_rem FROM FEED f, INFORMATION i
    WHERE f.ID = i.id
  ORDER BY f.ID, f.S_DATE;
  -- used NVL to prevent side-effect of null values


  SELECT * from RESULT_FEED;

Your approach with with trigger is not suitable in this situation. I think data is added seldom and query needed only in special cases. Of course there is some approaches to workaround mutating table(package variables,compound triggers, autonoumous transaction) but I think they only add perfomance problems to your database.

Answer 2

If this was my business problem, and I could start from scratch (which may not be possible in your case), I would keep the INFORMATION table as-is, I would remove the TOT_REM column from FEED, and I would create a view that looks like the current FEED table. You can write all the necessary logic in the view definition.

ADDED:

First, here is a view definition; it assumes the base tables INFORMATION and FEED are as described by the OP, with no TOT_REM column in FEED.

create view remaining_balance (id, s_amount, s_date, tot_rem) as
select i.id, f.s_amount, f.s_date, 
       i.tot_amount - nvl(sum(f.s_amount) over (partition by f.id order by f.s_date), 0)
from   information i left outer join feed f
                     on i.id = f.id
;

The view uses an outer join, to include the id's from the INFORMATION table that don't have any corresponding rows in FEED. (Then, to deal with nulls in the computation of TOT_REM, I use the nvl() function to convert NULL to 0.)

Here is an example of running the view:

 SQL> select * from information;

        ID TOT_AMOUNT
---------- ----------
         1        100
         2         20
         3         50

3 rows selected.

SQL> select * from feed;

        ID   S_AMOUNT S_DATE
---------- ---------- ----------
         1         10 2010-10-10
         1         10 2010-10-13
         1         30 2010-10-17
         1         10 2016-10-20

4 rows selected.

SQL> select * from remaining_balance order by id, s_date;

        ID   S_AMOUNT S_DATE        TOT_REM
---------- ---------- ---------- ----------
         1         10 2010-10-10         90
         1         10 2010-10-13         80
         1         30 2010-10-17         50
         1         10 2016-10-20         40
         2                               20
         3                               50

6 rows selected.

Now, a well-established method to enforce complex constraints is to use materialized views. Outright check constraints only work at the row level and can't be used when conditions involve more than one table. In the current problem the check is against two tables, and TOT_REM depends on other rows in the FEED table - so a constraint in the FEED table wouldn't work anyway.

The materialized view approach is to define a view like the one I created, as a materialized view, to define it with refresh fast on commit (so that the constraints are checked immediately after DML operations on the base tables), and to create a check constraint on the materialized view. In the problem at hand, that would be a check on TOT_RM >= 0.

Alas, refresh fast on commit is prohibited (at least as recently as Oracle version 11.2, which is what I have), when the view definition uses analytic functions. I used the analytic version of the sum() function, so this won't work.

It seems to make sense, however, to define a different materialized view, like below:

create materialized view remaining_balance (id, tot_rem) as
select i.id, i.tot_amount - f.sum_s_amount
from   information i inner join (select   id, sum(s_amount) as sum_s_amount 
                                 from     feed 
                                 group by id) f
                     on i.id = f.id
;

SQL> select * from remaining_balance;

        ID    TOT_REM
---------- ----------
         1         40

I don't use an outer join anymore, since this is only supposed to show remaining balances. I assume INFORMATION has a check constraint on TOT_AMOUNT to make sure it is non-negative, and id in FEED is primary key pointing to id in INFORMATION, so there's no additional information revealed by an outer join for this version of the view. (However, if desired, it can be made to include all id's).

Here you should be able to define the view with refresh fast on commit and to add a check constraint to the effect of tot_rem >= 0. Alas, I am not able to test it; advanced replication (required to create materialized view logs, which in turn are needed for refresh fast) is not available/enabled in the free Express version of Oracle I have. Try to experiment with this though - it may be the solution you need. Good luck!