Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Updating Field in Oracle through Forms for time stamp

Posted on 2007-11-26
8
Medium Priority
?
1,172 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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 explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

721 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