Home » RDBMS Server » Server Administration » Issue of Update for a record in a Database Block
Issue of Update for a record in a Database Block [message #370655] Mon, 17 January 2000 07:03 Go to next message
Ajay Madan
Messages: 8
Registered: January 2000
Junior Member
Hi !!
My application is on Forms 6.0/Oracle8i server. I have a Database Block(no CTRL blocks) on a Base Table which has a Delete Flag field.

Our Application requires that we update the delete flag field to 'Y' when there is a necessity to delete, instead of physically deleting the record from the database.

The delete flag field is not supposed to be displayed on the canvas. So it is set to canvas Null. Everytime I try to set this field to 'Y' and then commit, the transaction fails giving a message that
there is a primary key violation. This is happening bcos an insert is fired instead of an update. I come to understand that the type of SQL fired depends on the status of the record. An insert is fired if the
record status is 'INSERT' and an update is fired if the status is 'CHANGED'.

Inspite of me changing my field value, the record status value does not change to 'CHANGED'.

Can you tell me how do I issue an 'update' at will, when using a Database Block? So that I can update only those columns which I want changed.

Or is it that I am not doing a 'Select for Update' before updating that record?

Alternately, in case I explicitly write a SQL UPDATE How do I commit overriding the default commit processing?
Re: Issue of Update for a record in a Database Block [message #370657 is a reply to message #370655] Mon, 17 January 2000 08:55 Go to previous message
Paul
Messages: 164
Registered: April 1999
Senior Member
Ajay,
Several things you can check out - forgive
me if any are too obvious;

1. Can you update this flag (and for that matter,
can you update the record at all) in SQL/PLUS.
If not, your problem may be with table definition
or database triggers.

2. Could your application be attempting to update
the primary key as well as the delete flag, for
example incrementing a sequence number or replacing
a date that is part of the PK with SYSDATE. Hate to
admit it, but ran into a similar problem when I copied
the code from a PRE-INSERT trigger to a PRE-UPDATE
trigger and neglected to remove the code that grabbed
a sequence number and plugged it into the PK -- OOPS.

3. Are the block and item (of the delete flag) Database
properties 'Update Allowed' set to YES. I'm not sure,
but if either is NO, Forms may consider a change as
a new record.

4. Try setting the block Database 'Update Changed Columns
Only' property to YES, if its is currently NO. This
one is a stretch, not really sure how it would cause a
problem, unless combined with #2 above.

Hope this helps,
Paul
Previous Topic: Calling sendmail within SQL or PL-SQL
Next Topic: not a single-group group function
Goto Forum:
  


Current Time: Fri Oct 30 03:36:24 CDT 2020