Solved

Updating Field in Oracle through Forms for time stamp

Posted on 2007-11-26
8
1,150 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle -- identify blocking session 24 52
Oracle sql query 7 62
Distinct values from all columns in a table?? PL SQL 4 38
Use of Exception to end a Loop 3 33
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

810 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