Solved

Updating Field in Oracle through Forms for time stamp

Posted on 2007-11-26
8
1,161 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

752 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