How to write audit trigger
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
why write your own crap triggers when you can use dbms_fga
learned something new today (dbms_fga)
Originally Posted by davey23uk
its a good tool, lacks some certain things though - i.e it audits every user instead of a specified user
hi davey, is this package available on 8i?
new in 9i - in 8i you would have to use standard auditing. It always helps to include a version when posting
If you end up writing your own triggers I would strongly advise against using rowid. From the 8.1.7 concepts manual:
Much safer to use the primary key instead
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.
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
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.
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:
Last edited by TimHall; 02-16-2006 at 11:43 AM.
Click Here to Expand Forum to Full Width