update view

[barab]

Hi all,

First of all I would like to apologize for my english.

Kindly following the scenario :

CREATE OR REPLACE VIEW V_ACCOUNT (ACCOUNT_ID,ID,ACCOUNT_RATE,ACCOUNT_MAX,ACCOUNT_TOTAL)
AS
   SELECT  1, 2, 10 ,200 , 0 FROM DUAL
   UNION
   SELECT  1, 5, 12 ,150 , 0 FROM DUAL
   UNION
   SELECT  1, 9, 8  ,400 , 0 FROM DUAL
   UNION
   SELECT  2 ,1, 7  ,100 , 0 FROM DUAL
   UNION
   SELECT  2 ,3 ,5  ,200 , 0 FROM DUAL
ORDER BY 1,2

ACCOUNT_ID         ID ACCOUNT_RATE ACCOUNT_MAX ACCOUNT_TOTAL
---------- ---------- ------------ ----------- -------------
         1          2           10         200             0
         1          5           12         150             0
         1          9            8         400             0
         2          1            7         100             0
         2          3            5         200             0

CREATE TABLE ACCOUNT_AMOUNT(
ACCOUNT_ID NUMBER(10),
ACCOUNT_AMNT NUMBER(10))

I want when insert new reocrd (ACCOUNT_ID,ACCOUNT_AMNT ) to table ACCOUNT_AMOUNT , or update on column ACCOUNT_AMNT,
affect automatic on view V_ACCOUNT for same ACCOUNT_ID,as follows:

Insert into ACCOUNT_AMOUNT
   (ACCOUNT_ID, ACCOUNT_AMNT)
 Values
   (1, 9);
COMMIT;

select * from ACCOUNT_AMOUNT
 
ACCOUNT_ID ACCOUNT_AMNT
---------- ---------------
         1               9

select * from v_account
where account_id = 1

ACCOUNT_ID         ID ACCOUNT_RATE ACCOUNT_MAX ACCOUNT_TOTAL
---------- ---------- ------------ ----------- -------------
         1          2           10         200             0
         1          5           12         150             0
         1          9            8         400             0

When insert or update on table ACCOUNT_AMOUNT

make sum ACCOUNT_AMNT for all record same ACCOUNT_ID in table ACCOUNT_AMOUNT,

and ascending view Order by ID for same ACCOUNT_ID

and product sum ACCOUNT_AMNT with ACCOUNT_RATE for first record after ascending ID, of same ACCOUNT_ID
( sum ACCOUNT_AMNT * ACCOUNT_RATE ) , in my ex 9 * 10 ) = 90 = product result
and assign product result for ACCOUNT_TOTAL for same reocrd,
but i want check before assign ACCOUNT_TOTAL,
check product result with ACCOUNT_MAX :

if product result > ACCOUNT_MAX then

ACCOUNT_TOTAL for first record of id = ACCOUNT_MAX for first record of id
and remainder for ACCOUNT_AMNT calculate with ACCOUNT_TOTAL for second record of id , as follows:
ACCOUNT_TOTAL for second record of id =

1.( ACCOUNT_TOTAL for first record of id / ACCOUNT_RATE for first record of id ) as result1
2.( sum(ACCOUNT_AMNT ) - result1 as Remainder ACCOUNT_AMNT
3.( Remainder ACCOUNT_AMNT * ACCOUNT_RATE for second record of id) as ACCOUNT_TOTAL


else

ACCOUNT_TOTAL for first record of id = product result * ACCOUNT_RATE

except result  for view after insert
-----------

select * from v_account
where account_id = 1

ACCOUNT_ID         ID ACCOUNT_RATE ACCOUNT_MAX ACCOUNT_TOTAL
---------- ---------- ------------ ----------- -------------
         1          2           10         200            90
         1          5           12         150             0
         1          9            8         400             0
    
    
another insert :

Insert into ACCOUNT_AMOUNT
   (ACCOUNT_ID, ACCOUNT_AMNT)
 Values
   (1, 20);
COMMIT;    

select * from ACCOUNT_AMOUNT
ACCOUNT_ID ACCOUNT_AMNT
---------- ---------------
         1              20
         1               9

SUM ACCOUNT_AMNT = 29   FOR  ACCOUNT_ID  = 1          
1. SUM OF ACCOUNT_AMNT = 29
2. 29 * ACCOUNT_RATE FOR ID 2 (10) = 290 
4. 290 > 200 (ACCOUNT_MAX FOR ID 2)then 
   ACCOUNT_TOTAL FOR ID 2 = 
   200 (ACCOUNT_MAX FOR ID 2) % 10 (ACCOUNT_RATE FOR ID 2) = 20 
5. 29 - 20  = 9 as Remainder ACCOUNT_AMNT

6. I want 9 Remainder ACCOUNT_AMNT with second record ( ascending ID) = ID = 5
   9 * 12 (ACCOUNT_RATE FOR ID 5)  = 108  AS ACCOUNT_TOTAL FOR ID 2

except result  for view after insert
-----------

select * from v_account
where account_id = 1

ACCOUNT_ID         ID ACCOUNT_RATE ACCOUNT_MAX ACCOUNT_TOTAL
---------- ---------- ------------ ----------- -------------
         1          2           10         200           200 (29*10)=290 > 200 /10 rate = 20 >>
                                                              29 - 20 = 9 Remainder ACCOUNT_AMNT
         1          5           12         150           108 (9* 12) =  108 And so on ...
         1          9            8         400             0


UPDATE ACCOUNT_AMOUNT      
SET    ACCOUNT_AMNT = 35
WHERE  ACCOUNT_AMNT = 20
  
COMMIT;

select * from ACCOUNT_AMOUNT
ACCOUNT_ID ACCOUNT_AMNT
---------- ---------------
         1              35
         1               9

SUM ACCOUNT_AMNT = 44   FOR  ACCOUNT_ID  = 1 

except result  for view after update     
select * from v_account
where account_id = 1

ACCOUNT_ID         ID ACCOUNT_RATE ACCOUNT_MAX ACCOUNT_TOTAL          ** explain **
---------- ---------- ------------ ----------- -------------
         1          2           10         200           200  >> (44 * 10 ) = 440 >>  200 /10 rate = 20 >>
                                                                 44 - 20 = 22 Remainder ACCOUNT_AMNT
         1          5           12         150           150  >> (22 * 12 ) = 264 >>  150 /12 rate = 12.5 
                                                              >> 22 - 12.5 = 9.5 Remainder ACCOUNT_AMNT
         1          9            8         400            76  >> (9.5 * 8)  = 76  And so on ....



I tried :

CREATE OR REPLACE TRIGGER T_ACCOUNT_AMOUNT AFTER UPDATE OR INSERT
ON ACCOUNT_AMOUNT
REFERENCING NEW AS NEW OLD AS OLD
For Each Row
DECLARE 
TOTAL_ACCOUNT_AMNT NUMBER(10);
Begin 
    
  SELECT SUM(ACCOUNT_AMNT)
  INTO   TOTAL_ACCOUNT_AMNT
  FROM   ACCOUNT_AMOUNT
  WHERE  ACCOUNT_ID = :NEW.ACCOUNT_ID ;
  
 UPDATE V_ACCOUNT
  SET    ACCOUNT_TOTAL = TOTAL_ACCOUNT_AMNT
  WHERE  ACCOUNT_MAX  <= TOTAL_ACCOUNT_AMNT
  AND    ACCOUNT_MAX  != ACCOUNT_TOTAL
  AND    ACCOUNT_MAX  <= ACCOUNT_TOTAL + TOTAL_ACCOUNT_AMNT;
End T_ACCOUNT_AMOUNT;
/

Thanks in advance

 

[Dagon]

I haven't much idea what you're trying to achieve. All I can say is that what you have there won't work:

a) You can't read from ACCOUNT_AMOUNT in a FOR EACH ROW trigger. You'll get a mutating table error.

b) I'm lost as to how you think you can update a view which consists of:

CREATE OR REPLACE VIEW V_ACCOUNT (ACCOUNT_ID,ID,ACCOUNT_RATE,ACCOUNT_MAX,ACCOUNT_TOTAL)
AS
   SELECT  1, 2, 10 ,200 , 0 FROM DUAL
   UNION
   SELECT  1, 5, 12 ,150 , 0 FROM DUAL
   UNION
   SELECT  1, 9, 8  ,400 , 0 FROM DUAL
   UNION
   SELECT  2 ,1, 7  ,100 , 0 FROM DUAL
   UNION
   SELECT  2 ,3 ,5  ,200 , 0 FROM DUAL
ORDER BY 1,2