Home » Developer & Programmer » Forms » DML Returning Value, on-update and field update (Forms 6i, Oracle 10g)
DML Returning Value, on-update and field update [message #396426] Mon, 06 April 2009 10:47 Go to next message
didiera
Messages: 134
Registered: August 2007
Location: Mauritius
Senior Member
Hello again everyone,

I have asked a similar question in a previous post but this time, it's a bit different. I have a database block based on a table for which there exist triggers before Insert and Update respectively. I have 'created' and 'updated' fields which are populated as records are inserted or updated by users. These fields are represented on my data block in Forms, but when I change another value of a given record and do a save, then try to modify the record. I get the error "Record has already been modified by another user" - Obviously because the update trigger changed the value for the 'updated' field of the same record. The table behind this block also has a primary key for which block I set the DML Returning Value field to 'Yes' and identified the 'ID' field as primary key with an 'Initial Value' property of :sequences.my_sequence.nextval ...all this is just fine. But how do I work around the 'updated' field thing except from requerying the whole block after each save?
In fact I'm thinking of substituting the 'updated' field on the block by a formula-based thing...would that work (I don't mind if its not exactly in synch with it's table counterpart)?

regards,
Didier
Re: DML Returning Value, on-update and field update [message #396583 is a reply to message #396426] Tue, 07 April 2009 04:49 Go to previous messageGo to next message
cookiemonster
Messages: 12420
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you've got DML return set to yes then I very much doubt it's the database triggers that are causing your "Record has already been modified by another user".

Couple of other possibilities:
do you do any direct update statements on the table?
Have you got any code on your detail block (if it is master detail) that modifies items in the master block?
Re: DML Returning Value, on-update and field update [message #396663 is a reply to message #396426] Tue, 07 April 2009 09:01 Go to previous messageGo to next message
didiera
Messages: 134
Registered: August 2007
Location: Mauritius
Senior Member
Hi again Cookiemonster,

I'm afraid I was right. In fact it really had to do with that on-update trigger updating my 'updated' field on the record. See the commit from the block triggered it and until then I had to do an execute query on it in order to be able to further modify fields on it. So my workaround was to replace the plain database field 'updated' by a formula-based one which, for each row, would return the updated date value and it's working fine for me. No more requerying and tracking back which row I was. Just a plain commit-form in my 'save' button code and everybody's happy Razz

thanks anyway.

regards,
Didier
Re: DML Returning Value, on-update and field update [message #396681 is a reply to message #396426] Tue, 07 April 2009 10:26 Go to previous messageGo to next message
cookiemonster
Messages: 12420
Registered: September 2008
Location: Rainy Manchester
Senior Member
I didn't realise you were actually using an on-update trigger.

Does it use the update_record builtin or does it have an update statement?

If it's the later DML return is definitely not going to work.
Re: DML Returning Value, on-update and field update [message #396739 is a reply to message #396681] Tue, 07 April 2009 22:32 Go to previous messageGo to next message
didiera
Messages: 134
Registered: August 2007
Location: Mauritius
Senior Member
Hi


It actually only
select sysdate into :new.updated



regards,
Didier
Re: DML Returning Value, on-update and field update [message #396837 is a reply to message #396739] Wed, 08 April 2009 02:35 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Lose the on-update trigger. Use non-database items for ':new.updated' (and 'new' as a block name sucks) and populate it using the 'post-query' trigger.

David
Re: DML Returning Value, on-update and field update [message #396869 is a reply to message #396426] Wed, 08 April 2009 03:55 Go to previous messageGo to next message
didiera
Messages: 134
Registered: August 2007
Location: Mauritius
Senior Member
Hi

In fact I referred to the code in the ON-UPDATE (of the table at database level) trigger and the :new is actually the reference to the new value of the 'updated' field actually. Anyway my workaround is doing good.

thanks anyway
Didier
Re: DML Returning Value, on-update and field update [message #396872 is a reply to message #396426] Wed, 08 April 2009 04:24 Go to previous messageGo to next message
cookiemonster
Messages: 12420
Registered: September 2008
Location: Rainy Manchester
Senior Member
You really need to sort out your terminology.

There's no such thing as an on-update database trigger.
There is before update and after update.

On-update is a forms trigger that overrides the forms default update behavoiur.

I would assume your trigger is a before update row trigger.

If that's what you've got them dml return should definitely work.

If your workaround works great, but I suspect you have another problem you don't know about.
Re: DML Returning Value, on-update and field update [message #396916 is a reply to message #396872] Wed, 08 April 2009 06:57 Go to previous message
didiera
Messages: 134
Registered: August 2007
Location: Mauritius
Senior Member
yes you are right. Sorry for that Embarassed
I was in fact referring to the before update database trigger that faires before the update of a row in a table.


cheers,
Didier
Previous Topic: FRM-40505
Next Topic: Excel data to tables
Goto Forum:
  


Current Time: Thu Dec 08 06:28:03 CST 2016

Total time taken to generate the page: 0.09567 seconds