I have an Oracle 8i database, and I want to add transaction logging. I know I can do this with triggers, but I'm not quite sure how.
I read on a web page that in 8i, you can create triggers that fire when any database object is altered, rather than having to create triggers for ALL the database tables for doing transaction logging. However, I'm yet to find instructions for doing so. The article I saw this at was this link: http://www.inquiry.com/techtips/orac.../10min1100.asp
So how do people do transaction logging in their 8i databases? I want something better than audit trails. I want to know what rows changed, what the change was, and who made it.
Basically, you want to create a trigger that fires BEFORE INSERT/UPDATE/DELETE and saves your values to a table. At one client, we wanted to audit the values of the XYZ table. We created a table called XYZ_AUDIT that had the exact same columns as XYZ with the addition of USERNAME, AUDIT_DATE, AUDIT_ACTION. The AUDIT_ACTION was 'INSERT' for an insert statement, 'OLD' for the old row in an UPDATE statement, 'NEW' for the updated row in an UPDATE statement, and 'DELETE' for a deleted row.
I do have one additional question: Does anyone know how one might apply a trigger to all the tables in the database? I would obviously have to alter that trigger code and make it more generic, but I'm wondering if this could be done.