OraFAQ Forum: Forms » Commit the data on change record

Home » Developer & Programmer » Forms » Commit the data on change record (oracle 9i, dev6i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Commit the data on change record [message #498211]

Wed, 09 March 2011 04:26

ArkeologeN
Messages: 37
Registered: March 2011
Location: Karachi, Pakistan

Member

I'm getting some problem in my project. I've taken my form fields in tabular way. I also have the ID with auto-increment system there. I want that when user moves towards the 2nd row for inserting new record then the 1st record would be committed.

I'm getting my bank-id from database and making +1 increment and made that bank-id text box into display box.

When running my form then it is increasing one like this :
database record

BKID-00001

and with one increment it is showing this in my form display field of 1st row in tabular form.

BKID-00002

but when I moves towards the 2nd row it doesn't commits the form and gives the BANK-ID AS "BKID-00002" again like the above row.. I need urgent solution. Please help me..

Report message to a moderator

Re: Commit the data on change record [message #498224 is a reply to message #498211]

Wed, 09 March 2011 05:04

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

WHEN-NEW-RECORD-INSTANCE trigger might contain

COMMIT;

and that would do what you want.

Report message to a moderator

Re: Commit the data on change record [message #498225 is a reply to message #498224]

Wed, 09 March 2011 05:08

ArkeologeN
Messages: 37
Registered: March 2011
Location: Karachi, Pakistan

Member

where I insert this trigger? form level? block level? or item level?

Report message to a moderator

Re: Commit the data on change record [message #498226 is a reply to message #498225]

Wed, 09 March 2011 05:10

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

Records belong to blocks, so block level. If I was you though I'd use post rather than commit. That applies the data to the db without making it permanent. Then the users still have the option of whether or not to save their changes.

Report message to a moderator

Re: Commit the data on change record [message #498228 is a reply to message #498225]

Wed, 09 March 2011 05:11

ArkeologeN
Messages: 37
Registered: March 2011
Location: Karachi, Pakistan

Member

I inserted this trigger as WHEN-NEW-RECORD-INSTANCE on form, block and item level but failed..

Report message to a moderator

Re: Commit the data on change record [message #498229 is a reply to message #498228]

Wed, 09 March 2011 05:12

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

Failed doesn't really mean anything. What exactly happened?

Report message to a moderator

Re: Commit the data on change record [message #498230 is a reply to message #498228]

Wed, 09 March 2011 05:13

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Record level only.

Pay attention to Cookiemonster's suggestion (POST instead of COMMIT).

Report message to a moderator

Re: Commit the data on change record [message #498231 is a reply to message #498229]

Wed, 09 March 2011 05:14

ArkeologeN
Messages: 37
Registered: March 2011
Location: Karachi, Pakistan

Member

It is not committing. I want that when user moves towards the second row of tabular form then 1st row would be committed because I want to get +1 bank-id in second row and it'll just possible when 1st row would be committed.

Report message to a moderator

Re: Commit the data on change record [message #498232 is a reply to message #498231]

Wed, 09 March 2011 05:17

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

Did you have the trigger at the right level - block?
Did you get any error messages when you changed record?
Did you try querying the DB directly after changing record to see if the data had really been committed?

Report message to a moderator

Re: Commit the data on change record [message #498235 is a reply to message #498232]

Wed, 09 March 2011 05:21

ArkeologeN
Messages: 37
Registered: March 2011
Location: Karachi, Pakistan

Member

No.. I'm not getting any error. I'm getting the issue that.. my bank-ID becomes +1 in first row..means it becomes "BKID-00002" in 1st row.. after inserting bank name, when I moves towards the second row, it shows "BKID-00002" again.. not increasing +1 because it is picking the final value from database. and first row is not committed.

Report message to a moderator

Re: Commit the data on change record [message #498236 is a reply to message #498235]

Wed, 09 March 2011 05:23

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Then you are doing something wrong. COMMIT in WHEN-NEW-RECORD-INSTANCE trigger *will* commit changes.

How do you fetch that "BKID-0000X" into a new record?

Report message to a moderator

Re: Commit the data on change record [message #498237 is a reply to message #498236]

Wed, 09 March 2011 05:25

ArkeologeN
Messages: 37
Registered: March 2011
Location: Karachi, Pakistan

Member

SELECT 'BKID-'||LPAD(SUBSTR(MAX(BL_BANK_ID), 6,5)+1,5,'0') INTO :BL_BANK_ID FROM SBM_BANK_LIST;

I'm already using this query as WHEN-NEW-RECORD-INSTANCE on block level..I inserted commit with it.. but no result as I want..

Report message to a moderator

Re: Commit the data on change record [message #498240 is a reply to message #498237]

Wed, 09 March 2011 05:28

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Could you post whole trigger code?

Report message to a moderator

Re: Commit the data on change record [message #498241 is a reply to message #498240]

Wed, 09 March 2011 05:31

ArkeologeN
Messages: 37
Registered: March 2011
Location: Karachi, Pakistan

Member

WHEN-NEW-RECORD-INSTANCE

SELECT 'BKID-'||LPAD(SUBSTR(MAX(BL_BANK_ID), 6,5)+1,5,'0') INTO :BL_BANK_ID FROM SBM_BANK_LIST;
commit;

I done this on block level. As I just have just two fields in my this form so no other special trigger present there. well, here is WHEN-VALIDATE-ITEM triggers on ID and NAME item
ID :

DECLARE
	DUMY CHAR(1);
BEGIN
	SELECT 'Y' INTO DUMY
	FROM SBM_BANK_LIST
	WHERE BL_BANK_ID = :BL_BANK_ID;
	MESSAGE ('THIS BANK ID IS ALREADY IN USE');
	MESSAGE ('THIS BANK ID IS ALREADY IN USE');
	RAISE FORM_TRIGGER_FAILURE;
EXCEPTION
	WHEN NO_DATA_FOUND THEN
	NULL;
	WHEN TOO_MANY_ROWS THEN
	MESSAGE ('TOO MANY ROWS ARE FOUND!');
	MESSAGE ('TOO MANY ROWS ARE FOUND!');
	RAISE FORM_TRIGGER_FAILURE;
END;

NAME :

DECLARE
	DUMY CHAR(1);
BEGIN
	SELECT 'Y' INTO DUMY
	FROM SBM_BANK_LIST
	WHERE BL_BANK_NAME = :BL_BANK_NAME;
	MESSAGE ('THIS BANK IS ALREADY ADDED');
	MESSAGE ('THIS BANK IS ALREADY ADDED');
	RAISE FORM_TRIGGER_FAILURE;
EXCEPTION
	WHEN NO_DATA_FOUND THEN
	NULL;
	WHEN TOO_MANY_ROWS THEN
	MESSAGE ('TOO MANY ROWS ARE FOUND!');
	MESSAGE ('TOO MANY ROWS ARE FOUND!');
	RAISE FORM_TRIGGER_FAILURE;
END;

Report message to a moderator

Re: Commit the data on change record [message #498244 is a reply to message #498241]

Wed, 09 March 2011 05:44

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

OK, I created a table and a form based on that table. It contains a single item.

WHEN-NEW-RECORD-INSTANCE trigger:

SELECT 'BKID-'||LPAD(nvl(SUBSTR(MAX(BL_BANK_ID), 6,5), 0)+1,5,'0') 
  INTO :bl_bank_id 
  FROM sbm_bank_list;

COMMIT;

Form output (new records are created by simple DOWN key presses):

/forum/fa/8772/0/

/forum/fa/8772/0/

So, it obviously works OK. No idea what you might be doing wrong.

Attachment: new_bkid.png
(Size: 7.61KB, Downloaded 1287 times)

Report message to a moderator

Re: Commit the data on change record [message #498245 is a reply to message #498241]

Wed, 09 March 2011 05:44

ArkeologeN
Messages: 37
Registered: March 2011
Location: Karachi, Pakistan

Member

no-body can help me?

Report message to a moderator

Re: Commit the data on change record [message #498246 is a reply to message #498245]

Wed, 09 March 2011 05:46

ArkeologeN
Messages: 37
Registered: March 2011
Location: Karachi, Pakistan

Member

sir let me try this... I gives you answer for a while.. wait please..

Report message to a moderator

Re: Commit the data on change record [message #498250 is a reply to message #498246]

Wed, 09 March 2011 05:52

ArkeologeN
Messages: 37
Registered: March 2011
Location: Karachi, Pakistan

Member

http://picturespk.pk/images/rgh1299671503y.jpg

Sir this is the issue again.. no change at all..

Report message to a moderator

Re: Commit the data on change record [message #498251 is a reply to message #498250]

Wed, 09 March 2011 05:56

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

There's the FRM-40202 error there. Bank name must be entered, so COMMIT failed because required item was empty.

What happens if you modify code so that COMMIT is above SELECT? Such as

COMMIT;

SELECT 'BKID-'||LPAD(nvl(SUBSTR(MAX(BL_BANK_ID), 6,5), 0)+1,5,'0') 
  INTO :bl_bank_id 
  FROM sbm_bank_list;

Report message to a moderator

Re: Commit the data on change record [message #498254 is a reply to message #498251]

Wed, 09 March 2011 06:06

ArkeologeN
Messages: 37
Registered: March 2011
Location: Karachi, Pakistan

Member

Sir this is the issue in the initializing of the form...check bottom.. no change to save.. and it comes with beep..this is :

http://picturespk.pk/images/klz1299672279c.jpg

But my issues is solved now.. however the beep and below alert is not going right..
Here is the final result..

http://picturespk.pk/images/bfi1299672378d.jpg

Report message to a moderator

Re: Commit the data on change record [message #498256 is a reply to message #498254]

Wed, 09 March 2011 06:10

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Beep and a message can be suppressed by a little "trick": setting :SYSTEM.MESSAGE_LEVEL to a higher value. Here's an example:

declare
  l_current_msg_level number;
begin
  l_current_msg_level := :system.message_level;
	
  :system.message_level := 25;
    COMMIT;
  :system.message_level := l_current_msg_level;
  
  SELECT 'BKID-'||LPAD(nvl(SUBSTR(MAX(BL_BANK_ID), 6,5), 0)+1,5,'0') 
    INTO :bl_bank_id 
    FROM sbm_bank_list;
end;

[EDITED by LF: fixed code formatting]

[Updated on: Wed, 09 March 2011 06:48]

Report message to a moderator

Re: Commit the data on change record [message #498259 is a reply to message #498256]

Wed, 09 March 2011 06:17

ArkeologeN
Messages: 37
Registered: March 2011
Location: Karachi, Pakistan

Member

YEAH! perfectly it works.. You're great sir.. Thanks a lot for your precious time and kind help. I'm very very thankful to you..

Regards,
Hamza Waqas

Report message to a moderator

Re: Commit the data on change record [message #498283 is a reply to message #498237]

Wed, 09 March 2011 08:17

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

ArkeologeN wrote on Wed, 09 March 2011 06:25

SELECT 'BKID-'||LPAD(SUBSTR(MAX(BL_BANK_ID), 6,5)+1,5,'0') INTO :BL_BANK_ID FROM SBM_BANK_LIST;
I'm already using this query as WHEN-NEW-RECORD-INSTANCE on block level..I inserted commit with it.. but no result as I want..

I hate to rain on your parade after you think it is working, but this is an invalid way to get the maximum bank id in a multi-user environment. No doubt more than one person will get the same number at some point. You really should be using sequences.

Report message to a moderator

Re: Commit the data on change record [message #498350 is a reply to message #498283]

Wed, 09 March 2011 14:10

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

By all means. "MAX + 1" was discussed so many times; sorry for not pointing that out earlier, during the discussion so - thank you, Joy Division!

Just a note, if I may (a link, actually, so that I wouldn't have to repeat myself). Follow the "gapeless sequence numbers" link!

Report message to a moderator

Re: Commit the data on change record [message #498423 is a reply to message #498350]

Wed, 09 March 2011 23:03

ArkeologeN
Messages: 37
Registered: March 2011
Location: Karachi, Pakistan

Member

You've pointed the right thing in the linked note Sir. Could you please tell me how can I add the gapless sequence number system in my application. I'm just making the current application for single user so I'll not make any kind of issue yet!. However, my next project is for networked multi-user Application Software.

Please give me the link from where I can learn the gapeless sequence number system..or give me some basic guide here.. I would be very thankful to you Sir. Waiting for your kind and precious reply.

Regards,
Hamza Waqas

Report message to a moderator

Re: Commit the data on change record [message #498434 is a reply to message #498423]

Thu, 10 March 2011 00:08

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Shortly: use sequences. Won't be gapeless, but will certainly be unique and performance won't suffer.

Report message to a moderator

Previous Topic:	order entry application
Next Topic:	Update database item when non-database item is manually updated

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Feb 10 01:17:17 CST 2025