AW: Help with time stamps in SQL

["BirgittaHauser" <Hauser@xxxxxxxxxxxxxxx>]

Hi,

Triggers are great, but please keep in mind: 
Each time a trigger is executed a dynamic programm call with all overhead
will be performed.
If you have to do mass updates a dynamic program call will be executed for
each row (if you won't or can't specify FOR EACH STATEMENT), which will
decrease performance noticeably.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them?  Not training them
and keeping them!"



-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Raul A. Jager W.
Gesendet: Friday, February 02, 2007 19:19
An: Midrange Systems Technical Discussion
Betreff: Re: Help with time stamps in SQL


A "trigger" program will be your best option.  You can write it in RPG, 
and it will update the date and time stamp for every change you make to 
the record.  The SQL (and any other program) just need to do theyr work, 
the trigger will set the date and time.  Even if you udate the record 
using dfu.
You don't need to wory about the programs that already set the time, 
tehy will still work.
When you ADDPFTRG you can choose if the trigger will fire for every 
update or only if something changes.
____________________________________________________________________________
________________________
Valerio Vincenti wrote:

> I have a number of mass database updates where SQL would be very 
> convenient instead of writing RPG programs.
>
> The complication is that my UPDATE statement also needs to populate a 
> couple of audit fields: a 7 digit numeric field that holds the Last Change
Date (CYYMMDD format) and a 6 digit numeric field for the Last Change Time
(HHMMSS format).
> For example, a record updated on Jan 20, 2007 at 11:30:43AM should be 
> updated with Change Date = 1070120 and Change Time = 113043 (since we are
already in the third millennium, I don't need to be concerned with the zero
century digit for dates earlier than 2000).
> Is there a way to retrieve the current date and time into my UPDATE 
> statement and format them to obtain the time stamp fields that I need?
>
> Thank you!
>