Home » Developer & Programmer » Forms » Validation (Oracle 10g)
Validation [message #432107] Sat, 21 November 2009 05:10 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

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 #432113 is a reply to message #432107] Sat, 21 November 2009 05:40 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Please read http://www.orafaq.com/forum/t/88153/0/
Before posting....


sriram.Smile
Re: Validation [message #432132 is a reply to message #432107] Sat, 21 November 2009 09:53 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
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.

And what happens if 2 sessions takes "ot_adj_batch" is ocrrect but sum of batch qty is "less than or greater than adji_item_qty of ot_adj_item" (whatever that means)?

Carefully read the following topic: http://www.orafaq.com/forum/m/360742/102589/?msg_360742#msg_360742

Regards
Michel
Re: Validation [message #432133 is a reply to message #432107] Sat, 21 November 2009 09:54 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
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:39
Quote:
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 Go to previous messageGo to next message
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 #432438 is a reply to message #432433] Tue, 24 November 2009 04:20 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Dear Jrowbottom thanks for your answer can you give one example.But this need to be implemented at form level.
Re: Validation [message #432439 is a reply to message #432438] Tue, 24 November 2009 04:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What does Quote:
But this need to be implemented at form level.
mean?
Re: Validation [message #432442 is a reply to message #432439] Tue, 24 November 2009 04:30 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

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 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT 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
Previous Topic: how can i save pdf or phto using webutil
Next Topic: Logon to another database
Goto Forum:
  


Current Time: Fri Sep 30 08:56:26 CDT 2016

Total time taken to generate the page: 0.08976 seconds