Update trigger [message #621296] |
Tue, 12 August 2014 07:54 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
kilimanjaro
Messages: 151 Registered: May 2009 Location: Tanzania
|
Senior Member |
|
|
Hello friends
Please assist
I have a form which pops up with TRANSACTION_NUMBER as a global value with the following data block;
create table donationsx(
TRANSACTION_NUMBER VARCHAR(10)unique not null,
amount number(10)not null
)
/
Now when I want to make changes to amount,it wont allow me to save do to unique constraint (TRANSACTION_NUMBER);
how do I go around that?
I'm using forms 6i.
Thank you.
|
|
|
|
Re: Update trigger [message #621324 is a reply to message #621299] |
Tue, 12 August 2014 10:06 ![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) |
kilimanjaro
Messages: 151 Registered: May 2009 Location: Tanzania
|
Senior Member |
|
|
hello Cookiemonster
the form has two blocks and this is what i have on new_form_instance
:DONATIONSX.TRANSACTION_NUMBER:=:global.shn;
:DONATIONSX.AMOUNT:=:global.amount;
DECLARE
cursor c1 is
select TRANSACTION_NUMBER,target,attending_personnel,payment_mode ,
account_number,account_name,branch,bank_name,currency_type from DONATIONS;
begin
open c1;
loop
fetch c1 into :DONATIONS.TRANSACTION_NUMBER,
:DONATIONS.target,
:DONATIONS.attending_personnel,
:DONATIONS.payment_mode ,
:DONATIONS.account_number,
:DONATIONS.account_name,
:DONATIONS.branch,
:DONATIONS.bank_name,
:DONATIONS.currency_type;
exit when c1%notfound;
--IF :A.CHK='Y' THEN
--:b.no:=a_record.no;
--:b.name:=a_record.name;
next_record;
end loop;
end;
=========
The problem is I can NOT save any changes because of uniqueness of transaction_number of the first block.
Please asisst
[EDITED by LF: fixed typo; was "I can save"]
[Updated on: Wed, 13 August 2014 00:09] by Moderator Report message to a moderator
|
|
|
Re: Update trigger [message #621326 is a reply to message #621324] |
Tue, 12 August 2014 10:15 ![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 |
|
|
No. The problem is you are using a cursor to populate the block.
If you don't use execute_query to populate a block then as far as forms is concerned any data put in the block is a new record and it should create an insert statement for it. This in turn causes your unique key violation.
The correct solution to this problem is to use the default query mechanism to populate the block - execute_query.
|
|
|
Re: Update trigger [message #621327 is a reply to message #621326] |
Tue, 12 August 2014 10:17 ![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 |
|
|
Hmm - actually this is more complicated than I thought - though populating a block with a cursor is still wrong.
Is DONATIONSX a database block?
Where are values for the globals coming from?
Which items in the form are the users allowed to change?
|
|
|
Re: Update trigger [message #621328 is a reply to message #621327] |
Tue, 12 August 2014 10:21 ![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) |
kilimanjaro
Messages: 151 Registered: May 2009 Location: Tanzania
|
Senior Member |
|
|
Thank you cookiemonster
DONATIONSX is a datablock with two fields
TRANSACTION_NUMBER VARCHAR(10)unique not null,
amount number(10)not null
it is also a master block and it gets its global value from an open form .users are not allowed to change anything on DONATIONSX block.
the detail block is the one the cursor c1 is feeding in and all fields are changable.
|
|
|
Re: Update trigger [message #621329 is a reply to message #621328] |
Tue, 12 August 2014 10:26 ![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 asked if DONATIONSX is a database datablock. It's implied in your response that the answer is yes, but it'd help if you explicitly said so.
What is the relationship between the datablocks?
|
|
|
Re: Update trigger [message #621330 is a reply to message #621329] |
Tue, 12 August 2014 10:32 ![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) |
kilimanjaro
Messages: 151 Registered: May 2009 Location: Tanzania
|
Senior Member |
|
|
TRANSACTION_NUMBER is a primary key in both DONATIONSX,DONATIONS (detail block) and bank_tra (of which the global value comes from).
I think i need an insert trigger and update statements .Insert if the DONATIONSX table is empty and update if it has data.But I'm getting things wrong somewhere I guess.
|
|
|
Re: Update trigger [message #621331 is a reply to message #621330] |
Tue, 12 August 2014 10:42 ![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 |
|
|
Do you understand the difference between a database datablock and a non-database datablock? I've asked twice now if donationsx is a database datablock and you still haven't answered me.
|
|
|
|
|
Re: Update trigger [message #621368 is a reply to message #621337] |
Tue, 12 August 2014 21:40 ![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) |
kilimanjaro
Messages: 151 Registered: May 2009 Location: Tanzania
|
Senior Member |
|
|
I have tried this on key-commit trigger still it doesn't give me results i wanted.
declare
xp varchar(10);
begin
begin
select TRANSACTION_NUMBER into xp from DONATIONSX
where TRANSACTION_NUMBER=:DONATIONSX.TRANSACTION_NUMBER;
exception
when no_data_found then
null;
end;
if xp =:DONATIONSX.TRANSACTION_NUMBER then
update DONATIONSX
set TRANSACTION_NUMBER=XP
where TRANSACTION_NUMBER=:DONATIONSX.TRANSACTION_NUMBER;
--else
--insert into DONATIONSX
--values( ':DONATION',:DONATIONSX.amount );
end if;
end;
|
|
|
Re: Update trigger [message #621384 is a reply to message #621368] |
Wed, 13 August 2014 00:26 ![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.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
As both master and detail blocks are database blocks (i.e. based on a table), uniqueness is violated as soon as you do
:DONATIONSX.TRANSACTION_NUMBER := :global.shn;
:DONATIONSX.AMOUNT := :global.amount;
(if :global.shn's value already exists in the DONATIONSX table. Apparently, it does).
You populated its detail block using a loop. However, there's no WHERE clause (see cursor's SELECT statement) so you select all records from the DONATIONS table, regardless of DONATIONSX block's values.
Besides, DONATIONSX table containsTRANSACTION_NUMBER VARCHAR(10) unique not null column. UNIQUE + NOT NULL = PRIMARY KEY (not that it would solve anything here; I'm just saying).
In WHEN-NEW-FORM-INSTANCE (WNFI) trigger you should first check whether record in DONATIONSX table (WHERE TRANSACTION_NUMBER = :GLOBAL.SHN) already exists. If so, you should perform EXECUTE_QUERY on the DONATIONSX block. Create ONETIME_WHERE clause (with SET_BLOCK_PROPERTY), putting the appropriate WHERE clause in it. If it does NOT exist, then put global variables into block items.
As of the detail block, no doubt - you must abandon the LOOP idea and let Forms do its job. If you used a data block wizard to create master-detail relationship, Forms already prepared required code which will make sure that master-detail querying mechanism brings records from the DETAILS table into the DETAILS block. You don't have to code anything here.
Here's what I was talking about (untested):-- WNFI trigger
declare
l_transaction_number donationsx.transaction_number%type;
begin
-- I'm using MAX in order to avoid NO-DATA-FOUND. As this is unique value,
-- only one value would be returned anyway (if it exists)
select max(d.transaction_number)
into l_transaction_number
from donationsx d
where d.transaction_number = :global.shn;
if l_transaction_number is null then
-- record does NOT exist
:donationsx.transaction_number := :global.shn;
:donationsx.amount := :global.amount;
else
-- record already exists - fetch it!
set_block_property('donationsx', onetime_where, 'where transaction_number = ' || :global.shn);
go_block('donationsx');
execute_query;
end if;
end;
|
|
|
Re: Update trigger [message #621419 is a reply to message #621384] |
Wed, 13 August 2014 04:10 ![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) |
kilimanjaro
Messages: 151 Registered: May 2009 Location: Tanzania
|
Senior Member |
|
|
Hello Littlefoot
sorry for constant queries.Im stack in declaring my table. How do I do that?
this is my table
create table donationsx(
TRANSACTION_NUMBER VARCHAR(10)unique not null,
amount number(10)not null
)
/
Thank you.
|
|
|
|
Re: Update trigger [message #621426 is a reply to message #621419] |
Wed, 13 August 2014 05:06 ![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) |
kilimanjaro
Messages: 151 Registered: May 2009 Location: Tanzania
|
Senior Member |
|
|
declare
l_transaction_number donationsx.transaction_number%type;
begin
-- I'm using MAX in order to avoid NO-DATA-FOUND. As this is unique value,
-- only one value would be returned anyway (if it exists)
select transaction_number
into l_transaction_number
from donationsx
where transaction_number = :global.shn;
if l_transaction_number is null then
-- record does NOT exist
:donationsx.transaction_number := :global.shn;
:donationsx.amount := :global.amount;
else if l_transaction_number is not null then
:donationsx.transaction_number:=l_transaction_number
and :donationsx.amount=:global.amount;
-- record already exists - fetch it!
-- set_block_property('donationsx', onetime_where, 'where transaction_number = ' || :global.shn);
-- go_block('donationsx');
-- execute_query;
end if;
end;
the cursor blinks at the end; and so I cant compile
|
|
|
|
|
|
Re: Update trigger [message #621461 is a reply to message #621432] |
Wed, 13 August 2014 08:38 ![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) |
kilimanjaro
Messages: 151 Registered: May 2009 Location: Tanzania
|
Senior Member |
|
|
this is the table concerned and also the form where the global value comes from .Thank you.
create table donationsx(
TRANSACTION_NUMBER VARCHAR(10)unique not null,
amount number(10)not null
)
/
create table donations(
TRANSACTION_NUMBER VARCHAR(10)not null,
target varchar(50)not null,
attending_personnel varchar(15)not null,
payment_mode varchar(15)not null,--Bank,mobile transfer
account_number varchar(15)not null,--or mobile phone number
account_name varchar(20)not null,--or mobile phone name
branch varchar (20)not null,--or mobile operator
bank_name varchar(15)not null,--or mobile operator
currency_type varchar(3)not null-- TZS or USD
)
/
|
|
|
Re: Update trigger [message #621463 is a reply to message #621460] |
Wed, 13 August 2014 08:58 ![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 asked you to use code tags - that includes for DDL statements, don't make me ask again.
milikiel wrote on Wed, 13 August 2014 14:36
But Little foot code didn't come up with what I wanted.
In what way exactly does it not come up with what you want?
Bare in mind that we can't necessarily download and open any fmbs (I certainly can't, don't have a working form builder installation here).
|
|
|
Re: Update trigger [message #621480 is a reply to message #621463] |
Wed, 13 August 2014 12:44 ![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) |
kilimanjaro
Messages: 151 Registered: May 2009 Location: Tanzania
|
Senior Member |
|
|
/* next time i will code my scripts as you suggested*/.
declare
l_transaction_number donationsx.transaction_number%type;
l_amount donationsx.amount%type;
cursor c1 is
select DONATIONS.TRANSACTION_NUMBER,DONATIONS.target,DONATIONS.attending_personnel,DONATIONS.payment_mode ,
DONATIONS.account_number,DONATIONS.account_name,DONATIONS.branch,DONATIONS.bank_name,
DONATIONS.currency_type,DONATIONSx.TRANSACTION_NUMBER,DONATIONSx.amount from DONATIONS,DONATIONSX;
begin
-- I'm using MAX in order to avoid NO-DATA-FOUND. As this is unique value,
-- only one value would be returned anyway (if it exists)
select transaction_number,amount
into l_transaction_number, l_amount
from donationsx
where transaction_number = :global.shn;
if l_transaction_number is null and l_amount is null then
:donationsx.transaction_number :=:global.Shn;
:donationsx.amount:= :global.amount;
else if l_transaction_number is not null and l_amount is not null then
open c1;
loop
fetch c1 into :DONATIONS.TRANSACTION_NUMBER,
:DONATIONS.target,
:DONATIONS.attending_personnel,
:DONATIONS.payment_mode ,
:DONATIONS.account_number,
:DONATIONS.account_name,
:DONATIONS.branch,
:DONATIONS.bank_name,
:DONATIONS.currency_type,
:DONATIONSx.TRANSACTION_NUMBER,
:DONATIONSx.amount;
exit when c1%notfound;
next_record;
end loop;
-- here i want to say if :DONATIONSx.TRANSACTION_NUMBER is pulled from the table as cursor c1 shows then when changes
-- are made to other fields apart from :DONATIONSx.TRANSACTION_NUMBER ,donationsx.transaction_number should be upadated.
--This is last thing im requesting.Thank you. This statement here compiled successful .
-- else if l_transaction_number is not null and l_amount is not null then
-- UPDATE DONATIONSx
-- SET TRANSACTION_NUMBER=l_transaction_number;
-- end if;
end if;
end if;
end;
|
|
|
Re: Update trigger [message #621489 is a reply to message #621480] |
Wed, 13 August 2014 14:09 ![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 |
|
|
milikiel wrote on Wed, 13 August 2014 18:44/* next time i will code my scripts as you suggested*/.
Not next time. Now.
It takes 10 seconds to read how to use code tags. Failure to do so is just lazy.
milikiel wrote on Wed, 13 August 2014 18:44
-- here i want to say if :DONATIONSx.TRANSACTION_NUMBER is pulled from the table as cursor c1 shows then when changes
-- are made to other fields apart from :DONATIONSx.TRANSACTION_NUMBER ,donationsx.transaction_number should be upadated.
I don't understand that at all. Transaction_number is your key. Keys should generally not be updated.
If you want to be able to update the other columns then you must use execute_query to populate the block.
Populating a database datablock with a cursor is always wrong
|
|
|
|
|
Re: Update trigger [message #621516 is a reply to message #621515] |
Thu, 14 August 2014 00:15 ![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) |
kilimanjaro
Messages: 151 Registered: May 2009 Location: Tanzania
|
Senior Member |
|
|
Hello Littlefoot.
It didn't work because nothing Pope up.I had to query.Even the global value didn't come up.Can you please check my last query I posted here and advice from that last point?
Thank you.
|
|
|
Re: Update trigger [message #621518 is a reply to message #621515] |
Thu, 14 August 2014 00:29 ![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) |
kilimanjaro
Messages: 151 Registered: May 2009 Location: Tanzania
|
Senior Member |
|
|
@littlefoot .
The Idea of when new form instance execute query if condition is not true is good.But I don't know how to put it down.
Thank you.
|
|
|
|
Re: Update trigger [message #621812 is a reply to message #621519] |
Mon, 18 August 2014 10:35 ![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/6f539f43889b4b3e3fcb6c591073de8f?s=64&d=mm&r=g) |
CraigB
Messages: 386 Registered: August 2014 Location: Utah, USA
|
Senior Member |
|
|
Having read through this whole thread, I've got a few observations...
First; Your data blocks are Base-Table Blocks (BTB). Why are you manually populating them through code instead of letting Forms default functionality do the work for you? As others have mentioned, you should be using the Execute_Query() built-in instead of using a Cursor For Loop. For example, your When-New-Form-Instance (WNFI) trigger could be as simple as the following:
/* Sample When-New-Form-Instance trigger */
/* Code is untested, but should work. */
DECLARE
v_def_where VARCHAR2(250);
BEGIN
v_def_where := 'transaction_number = '||NAME_IN('GLOBAL.SHN')||' and amount = '||NAME_IN('GLOBAL.AMOUNT');
Set_Block_Property('DONATIONSX',DEFAULT_WHERE,v_def_where);
Execute_Query;
END;
This code sample assumes you have a Data Block Relationship created between your DONATIONS and DONATIONSX data blocks.
Second; If you are going to do this manually, fine...but you need to understand the ramifications of doing this. Manually populating a BTB will cause Forms to treat each record as a NEW record (because Forms didn't query the record into the block). Therefore, when you attempt to use Forms default functionality to save (Commit_Form) records to your table, Forms will attempt an INSERT - not an UPDATE. If you are going to manually display your data, then you MUST manually perform the DML by overriding the On-Insert, On-Update and On-Delete triggers. This means, you have to keep track of your changes so you know to do an Update versus an Insert - this is way more work that you should be doing and it makes your Form very buggy and hard to maintain.
Third, your code has major problems...
Quote:
cursor c1 is
select DONATIONS.TRANSACTION_NUMBER,DONATIONS.target,DONATIONS.attending_personnel,DONATIONS.payment_mode ,
DONATIONS.account_number,DONATIONS.account_name,DONATIONS.branch,DONATIONS.bank_name,
DONATIONS.currency_type,DONATIONSx.TRANSACTION_NUMBER,DONATIONSx.amount from DONATIONS,DONATIONSX;
This cursor produces a cartesian result set - all of the records from DONATIONS and all of the records from DONATIONSX - multiplied by the number of records in DONATIONS.
At the very least, your cursor should link the two tables by the TRANSACTION_NUMBER and you should be limiting the results by the :GLOBAL.SHN. For example:
...
CURSOR c1 (p_trans_num VARCHAR2) IS
SELECT DONATIONS.TRANSACTION_NUMBER, DONATIONS.target, DONATIONS.attending_personnel
,DONATIONS.payment_mode, DONATIONS.account_number, DONATIONS.account_name
,DONATIONS.branch, DONATIONS.bank_name, DONATIONS.currency_type
,DONATIONSx.TRANSACTION_NUMBER,DONATIONSx.amount
FROM donations, donationsx
WHERE donations.transaction_number = donationsx.transaction_number
AND donations.transaction_number = p_trans_num;
...
Then, when you open your cursor, you should pass the :GLOBAL.shn as a parameter to the cursor...
...
OPEN c1(NAME_IN('GLOBAL.SHN'));
LOOP
FETCH c1 INTO ...
Lastly, I strongly recommend you change your Form design to let Forms populate your data blocks. This makes your Forms easier to create and maintain and less buggy.
Hope this helps,
Craig...
|
|
|
Re: Update trigger [message #622127 is a reply to message #621812] |
Thu, 21 August 2014 05:12 ![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) |
kilimanjaro
Messages: 151 Registered: May 2009 Location: Tanzania
|
Senior Member |
|
|
Hello Craig
Thank you very much for your help.
I tried your solution and it compiled successful .Unfortunately it cant perform query
Ora-40505 unable to perform query.
Where you wrote 'NAME_IN',Aim I supposed to change it something else?
Thank you very much.
|
|
|
Re: Update trigger [message #622148 is a reply to message #622127] |
Thu, 21 August 2014 07:40 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Leave the name_in as it is.
When you get the error, click on display error on the runtime help menu.
Post the full error message from that here if you can't work out what the problem is.
|
|
|