wnri Trigger problem [message #640936] |
Fri, 07 August 2015 01:38 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/481ad83bfa916e6a93b0af5b8a5f5d53?s=64&d=mm&r=g) |
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 #640965 is a reply to message #640959] |
Fri, 07 August 2015 05:34 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/481ad83bfa916e6a93b0af5b8a5f5d53?s=64&d=mm&r=g) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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 #641039 is a reply to message #641035] |
Mon, 10 August 2015 01:55 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/162e216f329427776c437d2638523b1d?s=64&d=mm&r=g) |
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 #641053 is a reply to message #641050] |
Mon, 10 August 2015 05:07 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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 #641055 is a reply to message #641054] |
Mon, 10 August 2015 05:33 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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.
|
|
|
|
|
|