Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Updating Field in Oracle through Forms for time stamp

Posted on 2007-11-26
8
Medium Priority
?
1,175 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
7 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 440 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

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!

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

879 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