Wrong Updation in When-Validate-Trigger [message #595812] |
Sat, 14 September 2013 00:41 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/0d689c6949c672e88a0153ef6302f9e3?s=64&d=mm&r=g) |
Blankhero
Messages: 27 Registered: September 2013
|
Junior Member |
|
|
Hi All,
In when-Validate-Item trigger,I am checking a condition like
if %>=90 and days<=7 then status='Y'
else status='N'
It is working fine in 95% cases
But in Some cases it is wrongly updating
like even if both conditions are true status ='N'
This is happening in user side on rare occasions
so what we do currently is ask the user to delete that line and insert it again and it is working
What I need is how to recreate that scenario which is wrongly updating(Ofcourse,what is going wrong????)
Thnks
|
|
|
|
|
|
|
|
|
|
|
Re: Wrong Updation in When-Validate-Trigger [message #595859 is a reply to message #595857] |
Sun, 15 September 2013 04:51 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Blankhero wrote on Sun, 15 September 2013 12:04
SELECT TO_NUMBER (parameter2), TO_NUMBER (parameter3)
INTO l_discount_percentage, l_days_discount
FROM tnq_all_parameter_tb
WHERE parameter1 = 'CFA_CUST_ORD_ADVANCE';
What is the data type of columns parameter2 and parameter3 in table tnq_all_parameter_tb?
Why do you want to convert it to number.
The issue here might be due to the to_number conversion of l_discount_percentage and l_days_discount.
Quote: if l_amount_paid_percent >= l_discount_percentage and l_diff_days <= l_days_discount
then
:TJD_CFA_CUST_ORD_ADV_DTL_TB.DISCOUNT_STATUS := 'Y';
else
:TJD_CFA_CUST_ORD_ADV_DTL_TB.DISCOUNT_STATUS := 'N';
end if;
|
|
|
|
Re: Wrong Updation in When-Validate-Trigger [message #595876 is a reply to message #595872] |
Sun, 15 September 2013 13:09 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
The following will not solve your problem, but this:
select
round((:TJD_CFA_CUST_ORD_ADV_DTL_TB.ADVANCE_PAYMENT / :TJD_CFA_CUST_ORD_ADV_DTL_TB.CUSTOMER_ORDER_VALUE)*100,2)
into
l_amount_paid_percent
from
dual; should rather be rewritten so that you don't SELECT FROM DUAL, as
l_amount_paid_percent := round((:TJD_CFA_CUST_ORD_ADV_DTL_TB.ADVANCE_PAYMENT /
:TJD_CFA_CUST_ORD_ADV_DTL_TB.CUSTOMER_ORDER_VALUE) * 100, 2);
As of your problems, display all variables used in IF statement, just before you call it, such as
message('l_amount_paid_percent = ' || l_amount_paid_percent);
message('l_discount_percentage = ' || l_discount_percentage);
...
if ...
else
end if;
Doing so, you'd see which input values evaluate to which variable values and the reason your IF fails (or, should I rather say, does exactly what you told it to).
[Updated on: Sun, 15 September 2013 13:10] Report message to a moderator
|
|
|
Re: Wrong Updation in When-Validate-Trigger [message #595878 is a reply to message #595872] |
Sun, 15 September 2013 13:21 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Blankhero wrote on Sun, 15 September 2013 22:47If it is due to to_number can u explain why it happens rarely ?how to recreate that scenario ?
You mean to say I need to recreate the scenario which YOU are facing? It's impossible with such limited information. You should have some logging mechanism to know for which values you are facing the issue. All it needs is to dig in to the issue step by step, start with what Littlefoot suggested.
[Updated on: Sun, 15 September 2013 13:21] Report message to a moderator
|
|
|
|
Re: Wrong Updation in When-Validate-Trigger [message #595882 is a reply to message #595881] |
Sun, 15 September 2013 14:03 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Blankhero wrote on Mon, 16 September 2013 00:28I have a doubt ?what will happen if it does not trigger or triggered twice?
1. If no triggering event occurs, you would not see the input values in your output message.
2. If the triggering event happens twice, you would find two entries of input values in your output message(assuming you are logging it somewhere)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: Wrong Updation in When-Validate-Trigger [message #595931 is a reply to message #595930] |
Mon, 16 September 2013 05:19 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Let's cut to the chase.
For discount_status to get set to N the following statement must not be true:
if l_amount_paid_percent >= l_discount_percentage and l_diff_days <= l_days_discount
It'll be not true in the following cases:
a) l_amount_paid_percent < l_discount_percentage
b) l_days_discount < l_diff_days
c) any of the four variables is null.
l_amount_paid_percent will be null if either TJD_CFA_CUST_ORD_ADV_DTL_TB.ADVANCE_PAYMENT or :TJD_CFA_CUST_ORD_ADV_DTL_TB.CUSTOMER_ORDER_VALUE is null.
l_discount_percentage will be null if parameter2 is null.
l_diff_days will be null if either TJD_CFA_CUST_ORD_ADV_DTL_TB.CHEQUE_DATE or :TJD_CFA_CUST_ORD_ADV_DTL_TB.CUSTOMER_ORDER_DATE is null.
l_days_discount will be null if parameter3 is null.
Those are the options, it's up to you to work out which one is causing the issue.
|
|
|
|
|
|
|
|
Re: Wrong Updation in When-Validate-Trigger [message #595937 is a reply to message #595935] |
Mon, 16 September 2013 05:41 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Blankhero wrote on Mon, 16 September 2013 11:37Hi,
I can't get this point
"another trigger entirely is setting the item to N."
Means exactly what it says. Code in another trigger is setting DISCOUNT_STATUS to N. If there are no other triggers that set DISCOUNT_STATUS then you can ignore this.
Blankhero wrote on Mon, 16 September 2013 11:37
Since it is happening rarely whether it might have any dba related issue?
Like at that point something might have failed?
Such as?
If discount_status is not set or is set to Y and then gets set to N as a result of this trigger firing then the trigger is not failing and the above options I've laid out are the only possibilities.
|
|
|
Re: Wrong Updation in When-Validate-Trigger [message #595938 is a reply to message #595936] |
Mon, 16 September 2013 05:42 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Blankhero wrote on Mon, 16 September 2013 11:40whatever it may be why it is updating first time wrong ?even if it is 87.65 or 90.35 in both the occasions the status has to be either 'Y' or 'N'.
Something is rounding the values off in between runs?
|
|
|
|
|