| How to prevent duplicate record in multi record block [message #81660] |
Sun, 09 March 2003 05:46  |
Rishi
Messages: 63
Registered: January 2001
|
Member |
|
|
Hi All!!!
I have multi record block ..How to prevent duplicate records in multi record block...i want to prevent duplicate record on the basis of combination of column values(item_code,emp_code) should not duplicated..
Rishi
|
|
|
|
| Re: How to prevent duplicate record in multi record block [message #81662 is a reply to message #81660] |
Sun, 09 March 2003 06:15  |
JAFOD
Messages: 15
Registered: February 2003
|
Junior Member |
|
|
You can add BLOCK level PRE-INSERT and PRE-UPDATE that declares a numeric variable, then:
SELECT COUNT (*)
INTO v_counter
FROM table_you_are_using
WHERE item_code = :block_name.item_code_item_name
AND emp_code = :block_name.emp_code_item_name;
IF v_counter > 0 THEN
-- the combination already exists
-- take some action (display an Alert or a Message)
-- so the user knows what the problem is, then return
-- the cursor to one of the items that has been
-- duplicated and abort the insert/update
GO_ITEM('block_name.item_code_item_name');
Raise Form_Trigger_Failure;
END IF;
You can also add the same code (without the GO_ITEM()) to POST-TEXT-ITEM triggers on both items if you want to alert the user to the problem as soon as it occurs, but still keep the pre-insert and update triggers as a 'fail-safe' in case the user hits SAVE while in one of those items. Of course the ultimate 'fail-safe' here would be a database uniqueness constraint on that combination of columns.
Hope this helps,
Paul
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
