Re: Can this be done with a trigger?

Malcolm Dew-Jones wrote:
> DA Morgan (damorgan_at_psoug.org) wrote:
> : 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
>
> Perhaps I misunderstand it, but it seem to me that the FGA data you get
> doesn't seem to make it easy to answer questions such as
>
> "Did user X edit record Y?".
> "Which people have edited record X?".
> "What was the value of column Z in record X at 8:00 am on July 1st, what
> were the previous and next values, and who set them?"
> "What are all the edits that have been made to record X?"
>
> http://www.oracle.com/technology/pub/articles/nanda_fga_pt3.html mentions
> a couple of potential problems,
> - ... may lead to ... false positive entries in the audit trail
> - ... FGA records ... SQL statements ... but not the values before and
> after the change (which many of us might assume would be among the most
> important details you would want to know).
>
> The fga auditing seems most appropriate for "hacker" or "naughty
> programmer" security, along with numerous other log files, web logs, login
> logs, etc etc.
>
> $0.10

It does if you follow the examples available from Oracle and my website.

-- 
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.org