Re: select "triggers"

premmehrotra_at_hotmail.com (Prem K Mehrotra) wrote in message news:<43441e77.0404290820.5297ab8e_at_posting.google.com>...
> "Anurag Varma" <avdbi_at_hotmail.com> wrote in message news:<jf%jc.126856$e17.120642_at_twister.nyroc.rr.com>...
> > "Prem K Mehrotra" <premmehrotra_at_hotmail.com> wrote in message news:43441e77.0404281938.5ff05f82_at_posting.google.com...
> > > Oracle 9i introduced Fine Grained Auditing whereby when data is
> > > selected from a table,
> > > audit event handler can be invoked to track changes. I, however, want
> > > to do follwing:
> > >
> > > when a row is selected, I want to modify its data before it is
> > > returned to user.
> > > I cannot do it in application code because my application is third
> > > party. Is there something like a insert trigger for select (reverse
> > > of insert), where value returned can be modified, e.eg.m if data was
> > > stored in a column in compressed fsaion, I
> > > want to return it as uncompressed in a "select" trigger.
> > >
> > > I looked at FGA, I could not find anyway to aalter returned data.
> > >
> > >
> > > Appreciate any help,
> > >
> > > Thanks a lot.
> >
> > Re-think your FGA approach .. and instead create a view.
> > That takes care of your select.
> > "create or replace view myview as select col1, myfunc.uncompress(col2) from mytable".
> >
> > And use instead of triggers to take care of inserts/updates/deletes to the view.
> >
> > Anurag
>
>
> Anurag:
>
> Good suggestion. I did not think of the view earlier. Oracle has many
> restrictions on what can be done inside a functin which is accessed
> from sql.
> I hope those restriction do not cause problems when implementing
> uncmpression.
>
> I will still like to know whether in FGA audit handler, there is
> anyway to modify the returned value, that's why I had created this
> thread.

Anurag:

I followed above idea, but got stuck in update. I had to define a virtual column in view:

create or replace view ltr_body as
select uncompress(ltr_body_txt) ltr_bdoy_txt from table_orig

When I went to update I got this error:

ORA-01733: virtual column not allowed here ORA-06512: at line 13

Prem Received on Sun May 09 2004 - 16:48:59 CDT