Home » Developer & Programmer » Forms » How to handle duplicate data with ranges
How to handle duplicate data with ranges [message #290023] Thu, 27 December 2007 03:12 Go to next message
absolutemath
Messages: 11
Registered: December 2007
Location: Karachi
Junior Member
Dear all
I face a very little problem during building the duplication check on a field.
I have two columns in which user enter the ranges(i.e.) Range_From and Range_To
In Pre-Insert Trigger i wrote the duplication code and it working fine but when I am writing the duplication code in Pre-Update Trigger it populate my written message.

To eliminate duplication the code is as follows.
-------------------------------------------------------------
IF :RANGE_FROM IS NOT NULL THEN
SELECT COUNT(*)
INTO chk
FROM TIER_SCALE
WHERE TIER_CODE = :tier_scale.tier_code
AND FUND_CODE = :tier_scale.fund_code
AND :range_from BETWEEN RANGE_FROM AND RANGE_TO;

IF CHK > 0 THEN
showErrorMessage('Cannot Duplicate Data','Y');
END IF;
END IF;
---------------------------------------------------------------
Re: How to handle duplicate data with ranges [message #290027 is a reply to message #290023] Thu, 27 December 2007 03:22 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So, everything is OK in PRE-INSERT trigger, but the same code doesn't work in PRE-UPDATE trigger? What does this mean?
Quote:

... it populate my written message.
Populate with what?
Re: How to handle duplicate data with ranges [message #290032 is a reply to message #290027] Thu, 27 December 2007 03:44 Go to previous messageGo to next message
absolutemath
Messages: 11
Registered: December 2007
Location: Karachi
Junior Member
Same Code written in Pre-Update trigger but the problem is that
for example

Form display records

Range From Range To Percentage
1 1000 1
1001 2000 2

If user just reenter 1000 at the place where 1000 written

System gives message "Cannot Duplicate Data" (My Alert Message)
Re: How to handle duplicate data with ranges [message #290037 is a reply to message #290032] Thu, 27 December 2007 03:52 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
When user enters a value, trigger fires and SELECT COUNT(*) returns a number > 0 and, consequentially, shows a message.

What would you like it to do?
Re: How to handle duplicate data with ranges [message #290045 is a reply to message #290037] Thu, 27 December 2007 04:08 Go to previous messageGo to next message
absolutemath
Messages: 11
Registered: December 2007
Location: Karachi
Junior Member
Ya offcourse the same situation on which the system displaying message because count return 1 and it display message.

I want that system doesn't display the message because user update the same record and enter the same value that is already exist in the table
Re: How to handle duplicate data with ranges [message #290054 is a reply to message #290045] Thu, 27 December 2007 04:57 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, you might save the "original" item values in the PRE-RECORD trigger and compare them with a newly added ones. If they are the same, don't alert the user (as nothing, actually, changed).

You might also consider letting the database worry about duplicates - create some kind of uniqueness-checker (primary key / unique key / unique index) and stop worrying about it. Oracle will handle duplicates for you.
Previous Topic: What is session in Run_product
Next Topic: FRM-47313: Invalid query for hierarchial tree.
Goto Forum:
  


Current Time: Sat Dec 10 01:25:35 CST 2016

Total time taken to generate the page: 0.10455 seconds