Re: Can this be done with a trigger?
From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 06 Aug 2008 18:27:17 -0700
Message-ID: <1218072429.625249@bubbleator.drizzle.com>
>> 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 -
Date: Wed, 06 Aug 2008 18:27:17 -0700
Message-ID: <1218072429.625249@bubbleator.drizzle.com>
artmerar_at_yahoo.com wrote:
> 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?? >
It does ... you are reinventing the wheel: http://www.psoug.org/reference/dbms_fga.html
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Wed Aug 06 2008 - 20:27:17 CDT