Home » Developer & Programmer » Forms » Commit after insert into (Forms 6, XP)
Commit after insert into [message #589437] Fri, 05 July 2013 13:31 Go to next message
bluetooth420
Messages: 67
Registered: November 2011
Member
Hi

I am just making a audit table as well.

i have learnt the basics from here http://www.orafaq.com/forum/mv/msg/139165/381806/0/#msg_381806


My problem is that after inserting into audit table if i issue commit command then the table + unsaved data present on the form is also saved.

What i want is that i issue a command which save only inserted record in audit table, and should NOT save data present on the data entry form. (which will be saved later by other method/button).

Please guide.

Thanks



Re: Commit after insert into [message #589438 is a reply to message #589437] Fri, 05 July 2013 13:43 Go to previous messageGo to next message
cookiemonster
Messages: 10590
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use pragma autonomous_transaction
Re: Commit after insert into [message #589439 is a reply to message #589438] Fri, 05 July 2013 14:43 Go to previous messageGo to next message
bluetooth420
Messages: 67
Registered: November 2011
Member
Respected cookiemonster.

I will search how to use pragma autonomous_transaction in forms triggers.

Meanwhile i have seen FORMS_DDL('COMMIT') is accomplishing my objective.
Re: Commit after insert into [message #589572 is a reply to message #589439] Mon, 08 July 2013 15:49 Go to previous messageGo to next message
bluetooth420
Messages: 67
Registered: November 2011
Member
I made a procedure in forms 6 with the following code:

PROCEDURE Delete_audit IS
 pragma autonomous_transaction ;
begin
	
	 
insert into myaudit_invs (source, invdate) values ('Deleting Record', :inv0s.invdate);
delete_record;
  
END;


But it is giving me compilation error that "pragma autonomous_transaction is not a supported pragma".

Kindly guide me what i am missing.

I am using Oracle 10.2.0.1.0
Re: Commit after insert into [message #589574 is a reply to message #589572] Mon, 08 July 2013 16:07 Go to previous messageGo to next message
Littlefoot
Messages: 18844
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I suppose that Cookiemonster meant that you should do auditing on a database level (i.e. using a stored procedure or a database trigger), not in a form.
Re: Commit after insert into [message #589576 is a reply to message #589574] Mon, 08 July 2013 16:15 Go to previous messageGo to next message
bluetooth420
Messages: 67
Registered: November 2011
Member
Respected Littlefoot

it is sad that i am failed to give true understanding to Cookiemonster.
I respect his response and would like to deviate from his suggestion at the same time.

So, i want to use FORMS_DDL('COMMIT'). Do you think it will create any problem? (for me, it seems to be ok)
Re: Commit after insert into [message #589577 is a reply to message #589576] Mon, 08 July 2013 16:50 Go to previous messageGo to next message
cookiemonster
Messages: 10590
Registered: September 2008
Location: Rainy Manchester
Senior Member
FORMS_DDL('COMMIT') issues a commit to the db without kicking off standard forms save processing, so it won't apply changes in datablocks to the db. However, if the form has already made uncommitted changes to the DB (via the post command for example, or a dml statement in a trigger) then this will commit them.

pragma autonomous_transaction will only commit the changes made by the procedure using the pragma and nothing else.

My answer remains - use pragma autonomous_transaction. It's designed for this purpose.
Re: Commit after insert into [message #589596 is a reply to message #589577] Tue, 09 July 2013 01:49 Go to previous messageGo to next message
bluetooth420
Messages: 67
Registered: November 2011
Member
This is what i need exactly. I dont need standard forms saving process while using FORMS_DDL('COMMIT'). I am not using POST command and no other DML statement in pipeline. Hence a perfect solution for me.

cookiemonster wrote on Tue, 09 July 2013 02:50
FORMS_DDL('COMMIT') issues a commit to the db without kicking off standard forms save processing, so it won't apply changes in datablocks to the db. However, if the form has already made uncommitted changes to the DB (via the post command for example, or a dml statement in a trigger) then this will commit them.


I want to learn, how to use pragma autonomous_transaction. The form is not compiling the procedure. This mean that i have to make DB level procedure. Kindly guide me that then i have to pass "forms current value" to DB procedure to accomplish my objective ? am i tight in my thinking?

Quote:
pragma autonomous_transaction will only commit the changes made by the procedure using the pragma and nothing else.

My answer remains - use pragma autonomous_transaction. It's designed for this purpose.
Re: Commit after insert into [message #589597 is a reply to message #589596] Tue, 09 July 2013 01:54 Go to previous messageGo to next message
Littlefoot
Messages: 18844
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here is an example I created some time ago, which shows how to use pragma autonomous_transaction. Have a look.

You'd pass :inv0s.invdate as a parameter to a procedure.
Re: Commit after insert into [message #589670 is a reply to message #589597] Tue, 09 July 2013 16:09 Go to previous message
bluetooth420
Messages: 67
Registered: November 2011
Member
Littlefoot, cookiemonster.. Thank you very much. You are really shinning star of orafaq (along with djmartin and barbara). Plus many others too obveiously Smile

Previous Topic: Time in List Item
Next Topic: How to change image resolution in oracle forms.
Goto Forum:
  


Current Time: Wed Apr 23 16:42:46 CDT 2014

Total time taken to generate the page: 0.10407 seconds