| Validation [message #432107] |
Sat, 21 November 2009 05:10  |
|
|
Hi,
I have two tables one item table with primary key as below
ot_adj_item
adji_sys_id - primary key
adji_item_qty
sample date
adji_sys_id qty
1 10 pcs
second table linked to first table by foriegn key adjb_adji_sys_id
ot_adj_batch
adjb_adji_sys_id adjb_no qty
1 '101' 1
1 '102' 4
1 '103' 5
adjb_adji_sys_id references adji_sys_id of ot_adj_item
adjb_no
adjb qty
I want to put a validation or trigger to restrict the quantity in ot_adj_batch table that sum of batch qty must not be less than or greater than adji_item_qty of ot_adj_item while data entry.
Can anyone help
|
|
|
|
|
|
|
|
| Re: Validation [message #432133 is a reply to message #432107] |
Sat, 21 November 2009 09:54  |
 |
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
By the way the following answer to your previous topic is still valid:
Michel Cadot wrote on Sun, 15 November 2009 09:39Quote:Please tell me how to add the code tags as i dont know and there is no article on it.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Quote:Please help me writing this trigger.
Please explain each table purpose and what should do the trigger.
But as I said: you can't do it with a trigger unless you previously lock the table(s) before each insert or update.
Regards
Michel
[Updated on: Sat, 21 November 2009 09:55] Report message to a moderator |
|
|
|
| Re: Validation [message #432433 is a reply to message #432107] |
Tue, 24 November 2009 03:55 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
|
Your best bet would probably be to create an on commit refresh materialized view on the two tables, which contains the parent table total and the sum of the child tables values. You can then add a check constraint on this Mview requiring the two values to be the same.
|
|
|
|
|
|
|
|
| Re: Validation [message #432442 is a reply to message #432439] |
Tue, 24 November 2009 04:30 |
|
|
meaning to say i have tab based forms where there is one header .On header i am passing parameters and fetching the records on item tab and batch tab based on parameters passed on header.Now the user will go and edit data at item level and batch level.Its physical stock take form in inventory.If some item are not there in batch in system but exists physically u will add it .but the total of item quantity entered at item level must allways be equal to sum of batches qty.I am attaching the form.
|
|
|
|
| Re: Validation [message #433359 is a reply to message #432442] |
Tue, 01 December 2009 22:07 |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
I don't see a problem.
Use a 'Pre-Commit' at the Form level to test the various values. If you are testing an accumulation of an item then you will have to create that accumulating non-database item.
David
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
