Home » Developer & Programmer » Forms » Updating record must be an exception to the query. (Oracle Forms 10g)
Updating record must be an exception to the query. [message #576132] Thu, 31 January 2013 20:27 Go to next message
reppihsrow
Messages: 22
Registered: October 2012
Junior Member
SELECT COUNT(*)
INTO v_ctr
FROM leave_type_govt_service
WHERE (
      :block_name.min BETWEEN minimum AND maximum
OR    :block_name.max BETWEEN minimum AND maximum
      )
AND type = :block_name.type;
IF v_ctr > 0 THEN
MESSAGE('Minimum and Maximum Overlaps!');
MESSAGE('Minimum and Maximum Overlaps!');
END IF;

This code in my forms really works for inserting a new record.
But if I will update the record itself for example,
in the table 'Type 1'
Minimum Maximum
1         5 
6         10
11        15

and I will update the 6 - 10 to e.g. 7, It must update but my query includes 6 - 10 data so it raise the trigger failure.
Any idea?

Note: The AND condition in the last part is for unique types. Because even if e.g. 'Type 2' has a Min and Max data same as the 'Type 1' the trigger will not prompt.
Re: Updating record must be an exception to the query. [message #576143 is a reply to message #576132] Fri, 01 February 2013 00:06 Go to previous message
reppihsrow
Messages: 22
Registered: October 2012
Junior Member
Problem solved!
By just adding this.
	AND ROWID <> :leave_type_govt_service.rowid; 


Courtesy of 'http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/DAT8X/Default.aspx'.

Quote:
When you create a basetable block in Oracle Forms, it automatically includes the rowid in the block as an "invisible pseudoitem."

Very Happy
Previous Topic: Deploying Oracle Forms on Navigator different than IE.
Next Topic: Quit Excel from oracle forms
Goto Forum:
  


Current Time: Sat Jul 26 06:47:21 CDT 2014

Total time taken to generate the page: 0.08517 seconds