How to write audit trigger

[yxez]

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

[davey23uk]

why write your own crap triggers when you can use dbms_fga

[marist89]

why write your own crap triggers when you can use dbms_fga

learned something new today (dbms_fga)

[davey23uk]

its a good tool, lacks some certain things though - i.e it audits every user instead of a specified user

[yxez]

hi davey, is this package available on 8i?

[davey23uk]

new in 9i - in 8i you would have to use standard auditing. It always helps to include a version when posting

[hacketta1]

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

[yxez]

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

[TimHall]

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

[yxez]

thanks Tim