Home » Developer & Programmer » Forms » wnri Trigger problem (forms10)
wnri Trigger problem [message #640936] Fri, 07 August 2015 01:38 Go to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
Hallo...

I have 1 master and 2 detail blocks, and i am trying to disable one of the detail block when the condition matched.

i do have WNRI trigger in mater block:

declare
 var1   number;
 var2   number;
begin
 select count(*) into var1 from table1
   where exists (select 1 from table2, table3
                          where table2.id = table3.id and table2.cid = table3.cid)
   and table1.text in ('TODAY')
   and id = :master_blk.id;

 select count(*) into var2 from table1
   where exists (select 1 from table4, table3
                           where table4.id = tabel3.id
                            and table4.cid = table3.cid)
   and table1.text in ('TOMORROW')
   and id = :master_blk.id;

if var1 = 1 and var2 = 1 then
set_block_property ('detail_blk1', insert_allowed, property_true);
set_block_property ('detail_blk1', update_allowed, property_true);
set_block_property ('detail_blk2', insert_allowed, property_true);
set_block_property ('detail_blk2', update_allowed, property_true);

elsif var1 = 1 then
set_block_property ('detail_blk1', insert_allowed, property_true);
set_block_property ('detail_blk1', update_allowed, property_true);
set_block_property ('detail_blk2', insert_allowed, property_false);
set_block_property ('detail_blk2', update_allowed, property_false);

elsif var2 = 1 then
set_block_property ('detail_blk1', insert_allowed, property_false);
set_block_property ('detail_blk1', update_allowed, property_false);
set_block_property ('detail_blk2', insert_allowed, property_true);
set_block_property ('detail_blk2', update_allowed, property_true);
end if

end;


And when just condition 2 or condition 3 matched then it should active just one of the block which matched the condition, and user are not allowed to insert any data in the block which did not match the condition, and without problem can go to master block to choose other id.
But, the problem, i am having is, even just one condition is matched then it also activate the other block above or below and when i click in the block where the condition is not matched then i can not move my cursor to master block before i give some input in that block.

Can anyone please help? or any suggestion please.

regards,

Re: wnri Trigger problem [message #640946 is a reply to message #640936] Fri, 07 August 2015 03:21 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Put messages in the code and make sure var1 and var2 actually have the values you think they have.
Re: wnri Trigger problem [message #640949 is a reply to message #640946] Fri, 07 August 2015 03:37 Go to previous messageGo to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
Thank you for your reply CM,
Should I put the message after select each select Statement or, where should i put this message?

message ('hallo' || var1);
Re: wnri Trigger problem [message #640951 is a reply to message #640949] Fri, 07 August 2015 03:52 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
after each select is fine. You certainly shouldn't put in inside the IFs
Re: wnri Trigger problem [message #640956 is a reply to message #640951] Fri, 07 August 2015 04:28 Go to previous messageGo to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
Hallo CM,

yes, it did retrun value when matched, but i am still having the same Problem that, when just var2 matched then it should deactivate the detail_blk1, (insert/update_ property false)but still when i click to detail_blk1 then one field ist still active to take Input. Sad

can you please tell me where is my error?

thanking you again

regards,
Re: wnri Trigger problem [message #640958 is a reply to message #640956] Fri, 07 August 2015 04:38 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps you should modify the IF statement, something like
if var1 = 1 and var2 = 1 then
   enable block 1
   enable block 2
elsif var1 = 1 and var2 > 1 then
   enable block 1 
   disable block 2
elsif var1 > 1 and var2 = 1 then
   disable block 1
   enable block 2
end if


Now, my "var2 > 1" condition might need to be changed; could be "var2 = 0" or "var2 = 2" or ... you'll know it better.
Re: wnri Trigger problem [message #640959 is a reply to message #640956] Fri, 07 August 2015 04:45 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
palpali wrote on Fri, 07 August 2015 10:28
yes, it did retrun value when matched,


What values exactly?
Re: wnri Trigger problem [message #640965 is a reply to message #640959] Fri, 07 August 2015 05:34 Go to previous messageGo to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
it Returns, 0 or 1 when the condition matched, but the Problem is same...
is my query ok? or did i do something wrong to make join between tables?

Primary keys in the tables are:
table1: id, cid
tabel2: id, cid, text
table3: id, cid, ctext
table4: id, cid, ctext, text
Re: wnri Trigger problem [message #640968 is a reply to message #640965] Fri, 07 August 2015 06:27 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
We if the queries are giving the answers you expect then I see no reason to assume there's anything wrong with them.

To clarify - you're saying that when condition 2 is met it's still setting insert_allowed and update_allowed on both blocks?

Really you just need to comprehensively debug this.
Use messages to check it's going into the correct part of the IF statement.
Make sure there's no other code that's running that can also set those block properties.
Use messages to explicitly show the values of insert_allowed and update_allowed for both blocks before and after the trigger code is run.
Re: wnri Trigger problem [message #641033 is a reply to message #640968] Mon, 10 August 2015 01:32 Go to previous messageGo to next message
sanodani
Messages: 98
Registered: October 2014
Member
Hallo all....

When I tried to insert record in my Detail block in one item text field, i got an error :

"FRM-40654: Record has been updated by another user. Re-query to see Change."

what could be the reason for this?
please help...

regards,
Re: wnri Trigger problem [message #641035 is a reply to message #641033] Mon, 10 August 2015 01:39 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It means that Forms checked data that is stored in a database table and compared it to values you have in a form. As there was a mismatch, Forms raised an error saying that you are NOT viewing the most current data and asks you to perform re-query in order to fetch the last values from a table into your form.

Data has been changed by another user: for example, you executed query in a form; after that, someone else issued UPDATE and modified those values using SQL*Plus. Or, a database trigger modified some values; etc.
Re: wnri Trigger problem [message #641039 is a reply to message #641035] Mon, 10 August 2015 01:55 Go to previous messageGo to next message
sanodani
Messages: 98
Registered: October 2014
Member
Hi LF..
Thankx for your message, well I don't think that some other user has updated the same table as i am using.. but i would also like to share with you.. when i have also one Trigger in my detail-block, which is as follows:
if :System.block_status = 'NEW' then
 duplicate_record; 
 set_block_property('Detail_blk1', insert_allowed, property_true);
 :Detail_blk1.text1 := null;
set_record_property(:System.trigger_record, :System.trigger_block, Status, changed_status);
end if;

and when i changed the
Quote:
set_record_property(:System.trigger_record, :System.trigger_block, Status, query_status);
to
set_record_property(:System.trigger_record, :System.trigger_block, Status, changed_status);

then it allow to insert record, but give another error:
"FRM-92101: There was a failure in the Forms Server during Startup. This could happen due to invalid configuration....."
and even it saves the record what i have given...

can you please give me some Feedback/Suggestion?

thanking you in advance

regards,

Re: wnri Trigger problem [message #641048 is a reply to message #641039] Mon, 10 August 2015 03:52 Go to previous messageGo to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
Can anyone please tell me whether my query is correct?.. I am getting bit confusion with IN clause in my query.

select count(*) from table1
  where exists (select 1 from table2, table3 where table2.id = table3.id
                                               and table2.cid = table3.cid
                                               and table2.text1 = table3.text1
                                               and table2.user is not null )
  and table1.text in ('TODAY')
  and table1.cid in (select 1 from table3, table1 where table3.id = table1.id and table2.cid = table1.cid);


help please ?

r
Re: wnri Trigger problem [message #641049 is a reply to message #641048] Mon, 10 August 2015 03:56 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Can table1.cid ever be 1?
Re: wnri Trigger problem [message #641050 is a reply to message #641049] Mon, 10 August 2015 04:13 Go to previous messageGo to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
sry, I did not understand what do you mean :/ Can you please clear me ?
actually, I would like to get the record that matched my first Sub query and then second sub-query.
I have also tried, instead of
...... in (select 1 .......

and table1.cid in (select table3.cid from table3, table1 where where table3.id = table1.id and table2.cid = table1.cid);

Re: wnri Trigger problem [message #641053 is a reply to message #641050] Mon, 10 August 2015 05:07 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I meant exactly what I said.
You've written
AND COLUMN IN (SELECT 1 FROM QUERY)

If column is never 1 then that bit of the where clause is never true.

When you use IN the sub-select supplies a set of values and the column must match one of those values.
When you use EXISTS the sub-select just has to find matching rows and the select part of the sub-select is basically meaningless. As long as the FROM and WHERE are the same the SELECT can be absolutely anything in an EXISTS sub-query and still give the same results.
Re: wnri Trigger problem [message #641054 is a reply to message #641053] Mon, 10 August 2015 05:30 Go to previous messageGo to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
thank you very much for your great help/Suggestion CM.. Smile

well, i have corrected my query as:
select count(*) from table1
  where exists (select 1 from table2, table3 where table2.id = table3.id
                                               and table2.cid = table3.cid
                                               and table2.text1 = table3.text1
                                               and table2.user is not null )
                                               and table1.cid in (select table3.cid from table3, table1 where table3.id = table1.id and table2.cid = table1.cid)

  and table1.text in ('TODAY');

Is this correct way now? because i have already use EXISTS above thats why i don't know whether i can use EXISTS again somewhere or how i can use it?
I would thankful for your great Feedback again Smile

Re: wnri Trigger problem [message #641055 is a reply to message #641054] Mon, 10 August 2015 05:33 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can use EXISTS as many times as you want, you use it the same way each time.

What you've written appears to be valid syntax, but I have no idea if it does what you want.
Re: wnri Trigger problem [message #641057 is a reply to message #641055] Mon, 10 August 2015 05:56 Go to previous messageGo to next message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
Thank you very much CM for your great Suggestion Smile
I kept my query as per my previous post and i get the matched value as per the condition. Smile

But I am having now another Problem.. :/
in my Detail-block, I have two fields id and text1 and both are PK and there is one another PK which i don't Need to dispaly in my block, and when i try to save my record, i get the error:
"FRM-40508: ORACLE error: unable to INSERT-record."
I know why this error Comes, but i don't know how can i solve this error without displaying the 3 PK field in my block and save record.

Thanking you in agian.
regards,
Re: wnri Trigger problem [message #641058 is a reply to message #641057] Mon, 10 August 2015 06:05 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Is the other PK item that's not displayed in the block?
Have you assigned a value to it?
Re: wnri Trigger problem [message #641060 is a reply to message #641058] Mon, 10 August 2015 06:10 Go to previous message
palpali
Messages: 138
Registered: December 2014
Location: India
Senior Member
yes, the other 2 PKs are displayed and value are also assigned including 3rd.
Well, i did now, that i put the 3PK item in my block and made the VISIBLE = NO of 3 PK item field and now it works and allowed to save. Smile

Previous Topic: error frm-40803
Next Topic: Dummy Fields
Goto Forum:
  


Current Time: Thu Mar 28 12:22:21 CDT 2024