[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Oracle Trigger

Posted on 2012-09-05
21
Medium Priority
?
366 Views
Last Modified: 2012-10-04
Hi,
We have 2 tables tbl1 and tbl2 and have 2 trigger on tbl2. tbl1 contains following fields
supp
fcr_amt
curr_rate
loc_amt.
And tbl2 contains following fields
acct_code
fcr_amt
loc_amt
BEFORE Trigger on tbl2 will calculate update as follows
tbl2.loc_amt = tbl2.fcr_amt * tbl1.curr_rate
AFTER Trigger on tbl2 will calculate do as follows
tbl1.fcr_amt = sum(tbl2.fcr_amt);
tbl1.loc_amt = sum(tbl2.loc_amt);

The problem is sometimes tbl1.loc_amt will not match with tbl1.fcr_amt * curr_rate as all figures in tbl2 and tbl1 are rounded off to 2 decimal places. So we need to add the difference into any row of tbl2.loc_amt ( Prefer last record) . How to do this ? Please advise.
0
Comment
Question by:Jinesh Kumar Kochath
  • 9
  • 7
  • 3
  • +1
21 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 38371231
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.
0
 

Author Comment

by:Jinesh Kumar Kochath
ID: 38371273
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.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 38371332
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.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:Jinesh Kumar Kochath
ID: 38371348
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;
/
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 38371420
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.
0
 

Author Comment

by:Jinesh Kumar Kochath
ID: 38371545
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
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38371890
>>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) ;
0
 

Author Comment

by:Jinesh Kumar Kochath
ID: 38372041
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.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38372111
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;

Open in new window

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38372116
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

Open in new window

0
 

Author Comment

by:Jinesh Kumar Kochath
ID: 38378745
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.
0
 

Author Comment

by:Jinesh Kumar Kochath
ID: 38378913
Awaiting your reply. Pls..
0
 

Author Comment

by:Jinesh Kumar Kochath
ID: 38381923
Awaiting your reply... Is it not possible ??
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38394844
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.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38398848
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;

Open in new window

0
 

Author Comment

by:Jinesh Kumar Kochath
ID: 38402657
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..
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38405269
>>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?
0
 

Author Comment

by:Jinesh Kumar Kochath
ID: 38405327
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.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38405464
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.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38405683
>>>>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?
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 38405774
also, have you considered using views or virtual columns rather than triggers?

maintaining loc_amount in tbl1 based on actions against tbl2 doesn't really make sense.  
It should always be equal to

round(fcr_amt * curr_rate, 2)  

correct?  

if so, in 11g make it a virtual column.  in 10g change the table name and create a view with the original table name that exposes the calculated column.

A view could also support the loc_amount in tbl2, but you still need to define how the "last" record should be determined.

Also,  What if curr_rate was such that most of the records could be rounded up,  should the last record have a negative delta to adjust?  Or, should all records except the last be rounded down and then delta the last record up to compensate?
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question