Re: Can this be done with a trigger?

On Aug 5, 6:28 pm, DA Morgan <damor..._at_psoug.org> wrote:
> artme..._at_yahoo.com wrote:
> > Hi,
>
> > We have a table like this:
>
> >  CUSTOMER_ID
> >  PRODUCT_ID
> >  FORMAT
> >  STATUS
> >  DATE_ADDED
> >  DATE_MODIFIED
>
> > We also have a history table like this:
>
> > CUSTOMER_ID
> > PRODUCT_ID
> > FORMAT
> > STATUS
> > SOURCE
> > DATE_ADDED
>
> > We want to put a trigger on the first table such that when a record is
> > inserted or updated it will make the entry to the history table.
> > However, the problem is the 'SOURCE' column on the history table.  It
> > is not present or needed in the first table.
>
> > Entries into the first table are done through a PL/SQL program.  So,
> > what we want is that when the entry is made into the first table, it
> > fires the trigger to make the second entry.  But, how can this be done
> > if the columns do not match?
>
> > We do not want to code it in the PL/SQL program because then someone
> > can make an entry to the first table and we cannot record the history
> > on it, yet the SOURCE column only gets populated in the PL/SQL
> > program....
>
> > And, to make things more complicated, depending on what is being
> > UPDATED, we may need the values of :NEW or :OLD........
>
> > Is there a way to do this?
>
> In what version of Oracle?
>
> Assuming 9i or above you are reinventing the wheel: Use Fine Grained
> Auditing.
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

We are running Oracle 10g R2. 10.2.0.1.0 to be exact.......does this tell you anyhting?? Received on Tue Aug 05 2008 - 19:24:39 CDT