DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: How to write audit trigger

  1. #1
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681

    How to write audit trigger

    Hi Friends,

    I have a table EMP with 250 columns. I want to audit this
    table so that any updates on it will be recorded in an
    audit table with its corr. rowid. For example, if a a user updates
    the firstname column, then the audit table should only have
    the corres-ponding firstname column have value it in. If all
    columns were updated then the audit tables should contains
    the same all columns.


    Thanks a lot

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    why write your own crap triggers when you can use dbms_fga

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Quote Originally Posted by davey23uk
    why write your own crap triggers when you can use dbms_fga
    learned something new today (dbms_fga)
    Jeff Hunter

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    its a good tool, lacks some certain things though - i.e it audits every user instead of a specified user

  5. #5
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    hi davey, is this package available on 8i?

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    new in 9i - in 8i you would have to use standard auditing. It always helps to include a version when posting

  7. #7
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    If you end up writing your own triggers I would strongly advise against using rowid. From the 8.1.7 concepts manual:
    Every row in a nonclustered table is assigned a unique rowid that corresponds to the physical address of a row's row piece (or the initial row piece if the row is chained among multiple row pieces). In the case of clustered tables, rows in different tables that are in the same data block can have the same rowid.

    A row's assigned rowid remains unchanged unless the row is exported and imported using the Import and Export utilities. When you delete a row from a table and then commit the encompassing transaction, the deleted row's associated rowid can be assigned to a row inserted in a subsequent transaction.
    Much safer to use the primary key instead

  8. #8
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    Thanks friend,

    Yes u have a point there, but if some fraud want to mislead info, he
    will update the primary key column to different values, and u can not
    distinguish what was the orig value w/o referring to rowid or am I
    making sense?

    Im confused about this primary key thing.
    Most of our tables sa primary key and also have a column with unique key...can i make the unique key column as the primary key?
    instead of creating a specialize primary key like generating sequence.


    thanks

  9. #9
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    FYI:

    Fine Grained Auditing (FGA) was introduced in Oracle9i, but it was limited to auditing queries. It was only in Oracle 10g that it could audit DML also. In addition, the SQL and bind values were not captured until Oracle 10g. As a result, it was of little use in Oracle9i, but it's really cool in Oracle 10g. I had a bunch of articles on auditing, but I've compiled them into one here:

    http://www.oracle-base.com/articles/...ained_auditing

    Cheers

    Tim...
    Last edited by TimHall; 02-16-2006 at 12:43 PM.
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  10. #10
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    thanks Tim

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width