Re: global variables vs get statements

A copy of this was sent to "BAMAN MOTIVALA" <bmotivala_at_swipnet.se> (if that email address didn't require changing) On Wed, 23 Jun 1999 14:06:39 +0100, you wrote:

>Hello,
>
>I need to insert and update the userid and the sysdate into audit columns
>every time a user creates or changes a record.
>
>Which is more efficient:
>
>1) To create a PRE-FORM trigger with the following code:
>
>:global.username := get_application_property(username);
>:global.date := sysdate;
>
>And then reference these variables in PRE-INSERT and PRE-UPDATE triggers at
>the block
>level.
>
>Or.....
>
>2) Simply create PRE-INSERT and PRE-UPDATE triggers with the following
>code:
>
>:block.audit_username := get_application_property(username);
>:block.audit_date := sysdate;
>
>Does doing the GET_ statement and fetching the sysdate over and over again
>waste resources?
>

its virtually 6 one way, 1/2 dozen the other. the overhead is minimal HOWEVER -- the results from your example are very different.

setting a global date to sysdate ONCE sets the value of the date/time field at that point in time. on the other hand, assigning system to :block.audit_date gets the most current date/time. Consider if I fired up your application on monday morning and left it running until tuesday. Using method one above -- i would be auditing with mondays timestamp forever (until i shutdown and restart). Using method 2, i would be audited with a timestamp of 'right now'...

>Thanks,
>
>Baman
>
>
>

-- 
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation