| Duplicate record while saving(Urgent) [message #84913] |
Tue, 11 May 2004 18:40  |
Raj
Messages: 411
Registered: November 1998
|
Senior Member |
|
|
Hi,
I have a field seqno which is a database field.I need to check for duplicates in this field.It is not a primary key field.I am incrementing the values for this using :system.cursor_record.User is allowed to change the seqno.Before saving i have to check for the duplicates
Ex: if the seqno are 1,2 and 3.User tries to make 3 as 1,while saving i should display message as duplicate entry
How to go about these
|
|
|
|
| Re: Duplicate record while saving(Urgent) [message #84921 is a reply to message #84913] |
Wed, 12 May 2004 02:50  |
Himanshu
Messages: 457
Registered: December 2001
|
Senior Member |
|
|
Hi,
There are two things which you need to do, if your Form has a Multirecord block.
1.At the time of Commit go to Block.
Go to First record.
loop
Take the value of First record and
compare it with the values of each record.
loop
If match found then
L_Found:='Y'; ---Variable to be declared in
---Declare section.
Message('Error, duplicate record found');
else
next_record;
end if;
exit when :system.Last_record='TRUE';
end loop;
If L_Found='Y' Then
Exit;
End If;
exit when :system.Last_record='TRUE';
end loop;
The above mentioned code will ensure that there are nO duplicate records within the Block.
2.Again Go_Block.
Check the value for each changed or new record using a Select statement from Database.
If found in the DB then show error message.
Step 2 is for the Purpose that after querying in Multiple records block, if your user clears some of the records then the code of step1 will not find the records in the Block though they exists in the Table.
Alternative method and I feel a better one is to enforce a Unique constraint on your Column.
Regards
Himanshu
|
|
|
|
|
|
| Re: Duplicate record while saving(Urgent) [message #84934 is a reply to message #84925] |
Thu, 13 May 2004 00:55 |
Himanshu
Messages: 457
Registered: December 2001
|
Senior Member |
|
|
Good,
Now just look into the Oracle Error Manual and find out for message "unique key violated" or "check constraint violated".
I think it is ORA-02290 or ORA-2292.
Or just check in your form what Oracle error it gives.
The make use of following example code to display your message.
Regards
Himanshu
/*
** Example of declaring your own error-driven exceptions
*/
DECLARE
/*
** First declare the name of the exception
*/
cannot_del_parent EXCEPTION;
/*
** Then associate it with the ORA-2292 error
** which is "violated integrity constraint XYZ -
** child record found". Note error number is negative.
*/
PRAGMA Exception_Init (cannot_del_parent, -2292);
BEGIN
DELETE FROM PARENT_TABLE
WHERE PRIMARY_KEY_FIELD = :BLOCK.PK;
/*
** If we get here, then things went ok.
*/
EXCEPTION
/*
** If our error arises, then this exception
** will be raised. We can deal with it elegantly.
*/
WHEN cannot_del_parent THEN
Message('You cannot remove open '||
'order number'||:block.pk);
RAISE Form_Trigger_Failure;
END;
This method is best when the ORACLE error number itself is enough to allow your application to determine what happened. User-defined error messages can be returned from database triggers, procedures, or functions, as shown earlier with RAISE_APPLICATION_ERROR. Creating corresponding user-defined exceptions is a natural counterpart to trapping the errors you raise.
However, some errors returned by the kernel contain the name of the constraint (out of many possible ones) that has been violated, always returning a single ORACLE error number. An example of this would be:
ORA-02290: violated check constraint (SCOTT.C_CK)
|
|
|
|
| Re: Duplicate record while saving(Urgent) [message #85032 is a reply to message #84913] |
Mon, 24 May 2004 04:02 |
Anupam
Messages: 62
Registered: July 2001
|
Member |
|
|
u can create a when-validate-item trigger on seqno item
and check whether there is any duplicate entry
and if it is
raise form_trigger_failure exception
in this u can check at the time of entering the data and to replace with unique data
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
