Re: automatic update of a variable in pl/sql

"DA Morgan" <damorgan_at_x.washington.edu> wrote in message news:1111593806.118041_at_yasure...
> Laura Martignas wrote:
>
>> Hi,
>>
>> In PL/SQL, is it possible to declare a variable that will be updated each
>> time a field of a table will have its value changed ?
>>
>> For example, the attribute %TYPE update the type of a variable each time
>> the type of a field is changed. Is it possible to have the same thing not
>> for the type of the field, but for its value ?
>>
>> Thanks
>> Laura
>
> Not in the variable's declaration but the result can be accomplished by
> a different mechanism.
>
> First create a global variable by defining it in a package header and
> write a function in the package body that updates it.
>
> Then write an AFTER INSERT OR UPDATE trigger on the table setting the
> variable when the trigger fires and no exception is raised.
>
> If you do this test it carefully as there may be issues related to your
> implementation.
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace 'x' with 'u' to respond)

Daniel,

That was my first thought, but that approach is limited to changes made by the current session only, as the trigger would be writing to the package variable of the session that issues the DML.

I was thinking DBMS_ALERT would be a more likely candidate for this type of functionality -- but then that could get messy with the waits.

Perhaps one approach would be to always access the variable via a function, which checks for the event (without waiting) before returning the value. With a little forethought this approach could be implemented in a fairly generic way (perhaps even via a single overloaded package function).

++ mcs Received on Thu Mar 24 2005 - 05:06:16 CST