Oracle Trigger

i do not understand the question correctly. can you give 2 sample records in both tables and give the trigger code and the output you are currently getting. Mention what is incorrect in the output and then give the desired output - so that one can understand what is your trigger issue.

Here is the sample records
At Present
tbl1
SUPP        FCR_AMT          LOC_AMT   CURR_RATE
ABC Ltd    20,368.00        74,587.61   3.662

tbl2
ACCT_CODE            FCR_AMT       LOC_AMT
30030010010         4406             16134.77
30030010011         4406             16134.77
30030030001         4406             16134.77
60080010002         7150             26183.30

I need to store the details as follows
tbl1
SUPP        FCR_AMT          LOC_AMT   CURR_RATE
ABC Ltd    20,368.00        74,587.62   3.662

tbl2
ACCT_CODE            FCR_AMT       LOC_AMT
30030010010         4406             16134.77
30030010011         4406             16134.77
30030030001         4406             16134.77
60080010002         7150             26183.31

Hope you understood the problem.

ok. some decimal mismatch is there on what is coming now and what you needed.

Can you give your both triggers code fully for me to understand and help.

Trigger1 is as follows
CREATE OR REPLACE TRIGGER update_ft_detail_loc_amt
   BEFORE INSERT OR UPDATE
   ON acc_ft_detail
   FOR EACH ROW
DECLARE
   v_org_code    NUMBER (5);
   v_type_code   VARCHAR2 (50);
   v_vr_no       VARCHAR2 (50);
   curr_rate     NUMBER;
BEGIN
   v_org_code := :NEW.org_code;
   v_type_code := :NEW.fld_type_code;
   v_vr_no := :NEW.fld_vr_no;

   SELECT ftm_curr_rate
     INTO curr_rate
     FROM acc_ft_master
    WHERE org_code = v_org_code
      AND ftm_type_code = v_type_code
      AND ftm_vr_no = v_vr_no;

   :NEW.fld_amount := NVL (:NEW.fld_fcr_amount, 0) * NVL (curr_rate, 0);
   
   ft_master_para.v_loc_amt := NVL (ft_master_para.v_loc_amt, 0) + NVL (:NEW.fld_amount, 0);
   ft_master_para.v_fcr_amt :=   NVL (ft_master_para.v_fcr_amt, 0)  + NVL (:NEW.fld_fcr_amount, 0);

   IF     ROUND (ft_master_para.v_loc_amt, 2) <>  ROUND ((NVL (ft_master_para.v_fcr_amt, 0) * NVL (curr_rate, 0)),  2 )
      AND ft_master_para.v_srl_no = ft_master_para.v_srl_no1
   THEN
      :NEW.fld_amount :=  NVL (:NEW.fld_amount, 0)
         + (  ROUND ((NVL (ft_master_para.v_fcr_amt, 0) * NVL (curr_rate, 0)), 2 ) - ROUND (ft_master_para.v_loc_amt, 2)
           );
   END IF;
END;
/


The last IF block , i added to get the desired result. But not getting it..

Second trigger is as follows

CREATE OR REPLACE TRIGGER UPDATE_FT_MASTER
AFTER DELETE OR INSERT OR UPDATE
OF FLD_FCR_AMOUNT
ON ACC_FT_DETAIL
REFERENCING NEW AS NEW OLD AS OLD
declare

v_mast_total number;
v_det_total number ;
curr_rate number;


BEGIN


If ft_master_para.type_code not in ('JV') then


update acc_ft_master A set loc_amount = (SELECT -SUM(nvl(FLD_AMOUNT,0)) FROM ACC_FT_DETAIL B

WHERE B.ORG_CODE=A.ORG_CODE AND B.FLD_TYPE_CODE=A.FTM_TYPE_CODE AND B.FLD_VR_NO=A.FTM_VR_NO)

where a.fTM_type_code =ft_master_para.type_code and a.org_code = ft_master_para.org_code and a.ftm_vr_no = ft_master_para.vr_no;


update acc_ft_master A set fcr_amount = (SELECT -SUM(nvl(FLD_fcr_AMOUNT,0)) FROM ACC_FT_DETAIL B

WHERE B.ORG_CODE=A.ORG_CODE AND B.FLD_TYPE_CODE=A.FTM_TYPE_CODE AND B.FLD_VR_NO=A.FTM_VR_NO)

where a.fTM_type_code =ft_master_para.type_code and a.org_code = ft_master_para.org_code and a.ftm_vr_no = ft_master_para.vr_no;


End if;


SELECT MAX(rownum) INTO FT_MASTER_PARA.V_SRL_NO from acc_ft_Detail where
org_code = ft_master_para.org_code and fld_type_code = ft_master_para.type_code and fld_vr_no = ft_master_para.vr_no;


END;
/

By looking at the code, i am unable to pin point the issue with your code. Can you add some debug statements in both your triggers code to print that value into the screen ( use dbms_output.put_line(..) or log messages to a debug/log table to understand the value of the variables during the trigger code execution. you need to recompile the triggers code after you add debug messages.

After that once you run insert or update statements, then you should be able to detect the problem by looking at the log/debug messages.

Please find the script

Create table tbl1 (supp varchar2(500),
fcr_amt number(15,2),
loc_amt number(15,2),
curr_rate number(15,2));

Create table tbl2 (
supp varchar2(500),
acct_code number(15),
fcr_amount number(15,2),
loc_amt number(15,2));

CREATE OR REPLACE TRIGGER update_tbl2_loc_amt
   BEFORE INSERT OR UPDATE
   ON tbl2
   FOR EACH ROW
DECLARE
v_curr_rate number(15,3);
BEGIN

   SELECT curr_rate INTO v_curr_rate  FROM tbl1  WHERE supp = :new.supp;

   :NEW.loc_amt := NVL (:NEW.fcr_amt, 0) * NVL (v_curr_rate, 0);
   

END;
/


CREATE OR REPLACE TRIGGER UPDATE_tbl1_amt
AFTER DELETE OR INSERT OR UPDATE
OF fcr_amt
ON tbl2
REFERENCING NEW AS NEW OLD AS OLD

BEGIN

update tbl1 A set loc_amt = (SELECT SUM(nvl(loc_amt,0)) FROM tbl2 B
WHERE a.supp = b.supp) ;

update tbl1 A set fcr_amt = (SELECT SUM(nvl(fcr_amt,0)) FROM tbl2 B
WHERE a.supp = b.supp) ;

END;
/

select * from tbl1;

SUPP      FCR_AMT                      LOC_AMT        CURR_RATE
ABC      20,368.00       74,587.61                       3.662

select * from tbl2;

SUPP      ACCT_CODE      FCR_AMT      LOC_AMT

ABC      12                       4406      16134.77
ABC      13                       4406      16134.77
ABC      14                       4406      16134.77
ABC      15                       7150      26183.30

For the last record, LOC_AMT should save as 26183.31 instead of 26183.30. This .01 is calculated as
( tbl1.FCR_AMT * tbl1.CURR_RATE ) - tbl1.LOC_AMT
i.e (20,368.00 * 3.662 ) - 74,587.61

tbl1.FCR_AMT is sum(tbl2.FCR_AMT) and
tbl2.LOC_AMT is sum(tbl2.LOC_AMT)

How should i modify the trigger to get the output ?
Please help as this is little bit urgent issue..

Thanks

>>This .01 is calculated as  ( tbl1.FCR_AMT * tbl1.CURR_RATE ) - tbl1.LOC_AMT

The formula in update_tbl2_loc_amt isn't the same as the formula you listed above.

I'm not seeing how you get your numbers.  Based on the code in update_tbl2_loc_amt you are getting the correct result:  7150*3.662=26183.3

Also, you can simplify the second trigger with a single update:
update tbl1 A set (loc_amt,fcr_amt) = (SELECT SUM(nvl(loc_amt,0)),SUM(nvl(fcr_amt,0)) FROM tbl2 B
WHERE a.supp = b.supp) ;

Formula in triggers are correct.
i.e.

tbl2.fcr_amount will be entered by the user. tbl2.loc_amt will be calculated by the trigger update_tbl2_loc_amt. Means
tbl2.loc_amt = tbl2.fcr_amt * tbl1.curr_rate

tbl1.fcr_amt and tbl1.loc_amt will not be entered by the user and will be calculated by the trigger UPDATE_tbl1_amt. It means,
tbl1.fcr_amt = sum(tbl2.fcr_amt) (i.e 20,368.00 )
tbl1.loc_amt = sum(tbl2.loc_amt) (i.e. 74,587.61)

But when the user doing 20,368.00 * 3.662, he is getting 74,587.62 and the value storing in tbl1.loc_amt is 74,587.61. So there is a difference of .01 is there. To avoid this situation, tbl1.loc_amt should always be equal to sum(tbl2.loc_amt). So i need to add the difference .01 to the tbl2.loc_amt of last record i.e 26183.31 .At present tbl2.loc_amt  of last record is 26183.30. I need to change it to 26183.31.

Hope you understood.
Thanks.

Based on your setup you really can't just change numbers to make up for a rounding error.

If you remove the automatic rounding everything appears to work itself out.  The 74587.62 is actually being automatically rounded from 74587.616.

Remove the rounding from the test and see what you get:

--drop table tbl1 purge;
Create table tbl1 (supp varchar2(3),
fcr_amt number,
loc_amt number,
curr_rate number);

--drop table tbl2 purge;
Create table tbl2 (
supp varchar2(3),
acct_code number,
fcr_amt number,
loc_amt number);

CREATE OR REPLACE TRIGGER update_tbl2_loc_amt
   BEFORE INSERT OR UPDATE
   ON tbl2
   FOR EACH ROW
DECLARE
v_curr_rate number(15,3);
BEGIN
	dbms_output.put_line('row trigger');
	SELECT curr_rate INTO v_curr_rate  FROM tbl1  WHERE supp = :new.supp;
	:NEW.loc_amt := NVL (:NEW.fcr_amt, 0) * NVL (v_curr_rate, 0);
END;
/


CREATE OR REPLACE TRIGGER UPDATE_tbl1_amt
AFTER DELETE OR INSERT OR UPDATE
OF fcr_amt
ON tbl2 
REFERENCING NEW AS NEW OLD AS OLD
BEGIN
	dbms_output.put_line('table trigger');
	update tbl1 A set (loc_amt,fcr_amt) = (SELECT SUM(nvl(loc_amt,0)),SUM(nvl(fcr_amt,0)) FROM tbl2 B
		WHERE a.supp = b.supp) ;
END;
/

insert into tbl1 values('ABC',20368.00,74587.61,3.662);
insert into tbl2 values('ABC',12,4406,16134.77);
insert into tbl2 values('ABC',13,4406,16134.77);
insert into tbl2 values('ABC',14,4406,16134.77);
insert into tbl2 values('ABC',15,7150,26183.30);
commit;

select * from tbl1;
select * from tbl2;

Select allOpen in new window

oops...  forgot my output

select * from tbl1;

ABC      20368  74587.616      3.662

select * from tbl2;

ABC         12       4406  16134.772
ABC         13       4406  16134.772
ABC         14       4406  16134.772
ABC         15       7150    26183.3

Select allOpen in new window

Thank you for your support. User need all the amount in 2 decimal places only. Thats why i can't  increase the no of decimal places.  In your above eg; if i am rounding, the will looks like
select * from tbl1;

ABC      20368  74587.62      3.662

select * from tbl2;

ABC         12       4406  16134.77
ABC         13       4406  16134.77
ABC         14       4406  16134.77
ABC         15       7150   26183.30

In this case, sum(tbl2.loc_amt) i.e 74587.62 will not be equal to tbl1.loca_amt 74587.61

Please advise.

Awaiting your reply. Pls..

Awaiting your reply... Is it not possible ??

Sorry for the delay in responding.  We are moving offices and our systems were shut down.  

Based on your numbers, it is a rounding issue.  There really isn't anything you can do to 'fix' this.

How do you want to handle it if everyting is set to two decimals?  From my output above the math results in 74587.616.  When converted to two places, it rounds up.

Now you can tell Oracle to not round it but I don't think this will solve the problem in all instances and you will eventually see an issue.

I'll try to get some time later to check into this.

Based on this test case I'm getting 74587.61 in tbl1.loc_amt.

The only tweak I made was the column for curr_rate in tbl1.

drop table tbl1 purge;
Create table tbl1 (supp varchar2(3),
fcr_amt number(15,2),
loc_amt number(15,2),
curr_rate number(15,3));

drop table tbl2 purge;
Create table tbl2 (
supp varchar2(3),
acct_code number,
fcr_amt number(15,2),
loc_amt number(15,2));

CREATE OR REPLACE TRIGGER update_tbl2_loc_amt
   BEFORE INSERT OR UPDATE
   ON tbl2
   FOR EACH ROW
DECLARE
v_curr_rate number(15,3);
BEGIN
	SELECT curr_rate INTO v_curr_rate  FROM tbl1  WHERE supp = :new.supp;
	:NEW.loc_amt := NVL (:NEW.fcr_amt, 0) * NVL (v_curr_rate, 0);
END;
/


CREATE OR REPLACE TRIGGER UPDATE_tbl1_amt
AFTER DELETE OR INSERT OR UPDATE
OF fcr_amt
ON tbl2 
REFERENCING NEW AS NEW OLD AS OLD
BEGIN
	update tbl1 A set (loc_amt,fcr_amt) = (SELECT SUM(nvl(loc_amt,0)),SUM(nvl(fcr_amt,0)) FROM tbl2 B
		WHERE a.supp = b.supp) ;
END;
/

insert into tbl1 values('ABC',20368.00,74587.61,3.662);

insert into tbl2 values('ABC',12,4406,16134.77);
insert into tbl2 values('ABC',13,4406,16134.77);
insert into tbl2 values('ABC',14,4406,16134.77);
insert into tbl2 values('ABC',15,7150,26183.30);
commit;

select * from tbl1;
select * from tbl2;

Select allOpen in new window

Is there any option to check whether the current record is the last record ?

I mean, in the trigger of tbl2, it is calculating

 :NEW.loc_amt := NVL (:NEW.fcr_amt, 0) * NVL (v_curr_rate, 0);

Is there any possibility to check whether this :new.loc_amt is the amount of last record in that table ? for eg; :new.last_record = 'true' something like that ?

In that case i wil get the result..

Thanks..

>>Is there any option to check whether the current record is the last record ?

In a row trigger there is no first or last.  There is only 'current'.  It fires for each row and as far as it knows, it is the only row.

Where does this question stand now?  Based on my last post, is it not returning the correct results?

In your previous example,

       tbl1.loc_amt    = 74587.61 and
sum(tbl2.loc_amt)  = 74587.61  

Logically it is correct. But

tbl1.fcr_amt  *  curr_rate i.e
    20368.00  *  3.662        is 74587.62  not 74587.61  . There is 0.01 difference is there. If i could adjust this 0.01 differenc with loc_amt of any tbl2 record, my issue will be cleared.

Is there any option for that ??
Thanks.

I'm starting to get confused here.

>>20368.00  *  3.662        is 74587.62  

Where are you doing this?  If the table values are correct, then they are correct aren't they?

>>Is there any option for that ??

Not based on your triggers.  It is a rounding issue so your options are not to round or to account for the necessary decimal places.

I'll see if I can find some additional Experts that might be able to help you more with this.

>>>>Is there any option to check whether the current record is the last record ?

what are you considering the last record in tb2?    
the one with largest fcr_amt?
the one with the largest acct_code?

some other rule?

don't say "the last record entered" because there is nothing in your table to keep track of that.  "Last" must be defined by some values stored in the table.  Which value is it?