Solved

Updating Field in Oracle through Forms for time stamp

Posted on 2007-11-26
8
1,159 Views
Last Modified: 2013-12-19
Hi Folks,

   What would be the best way to updatea field in the database through forms with timestamp?
If we use the initial value property of a date datatype field with $$DATETIME$$, then only when a new record is entered, the field is populated. What would be the possible solution in irder to update the same filed so that every time a record is updated the field is populated?

TIA

hayub
0
Comment
Question by:hayub
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 9

Expert Comment

by:joebednarz
ID: 20351169
I would create an INSERT trigger for the table.  Something like this:

CREATE OR REPLACE TRIGGER orders_before_insert
BEFORE INSERT
    ON orders
    FOR EACH ROW

BEGIN

    -- Update create_date field to current system date
    :new.create_date := sysdate;

END;
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20351170
Put the foll. statements in the KEY-COMMIT trigger, before the APP_STANDARD.EVENT('KEY-COMMIT'); statement.

:BLOCK_NAME.LAST_UPDATE_DATE := SYSDATE;
:BLOCK_NAME.LAST_UPDATED_BY  := FND_PROFILE.VALUE('USER_ID');
0
 
LVL 9

Expert Comment

by:joebednarz
ID: 20351193
oh, sorry.  Just saw that you were wanting TIMESTAMP:

:new.create_date := SYSTIMESTAMP;
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 32

Expert Comment

by:awking00
ID: 20351208
Not sure I understandi the question, but perhaps you can create an after update trigger to accomplish your intent. Can you show the relevant table structure with some sample values and what you expect it to look like subsequent to the update?
0
 

Author Comment

by:hayub
ID: 20389063
Hi Folks,

      Thanks for your comments. I'm using the following code on WHEN_PRESS_BUTTON trigger:

commit_form;
begin
update pers
set time_stamp=sysdate;
commit;
end;

It does updates my time_stamp field, but at the same time it locks the whole data block so that we cannot update any fields in the datablock and I have to run the query again or refresh the form to further update more fields in that data block.

Any ideas?
0
 
LVL 18

Accepted Solution

by:
Jinesh Kamdar earned 110 total points
ID: 20402167
How about moving the COMMIT_FORM after the UPDATE? Did u try that approach.
Not sure why WHEN-BUTTON-PRESS should lock the block though.

begin
update pers
set time_stamp=sysdate;
commit;
end;
commit_form;
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20910325
Forced accept.

Computer101
Community Support Moderator
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Row_number in SQL 6 45
join a table with user_tab_columns in oracle 3 68
join actual table rows based on the column 25 31
Updating a temp table inside a PL/SQL block 3 51
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question