new at triggers

I want to write a trigger that will show me the following:

who makes changes to a table(s) & when
which field(s) changed
the before and after values of said field(s)

my pl/sql skills are limited, and writing trigers is completely new to me.

Advice anyone?


thanks in advance

Dunno how you would get the before and after values of the fields that have changed, but as far as who makes the changes is concerned, you can audit them.

You can do it in trigger.
Use :NEW and :OLD in the trigger. All you need is another table where you always insert a row with old value and new value, the user id, data and time stamp.

You would write an AFTER INSERT, UPDATE, DELETE trigger. Inside this trigger, you would inspect the :new and :old values of each field to see if they have changed. If they have changed, you would insert the appropriate information into your audit table.

I did something similar to this before. Basically, we had a table TABLE_A and we wanted a history of what changes were made to the data. We created another table AUDIT_TABLE_A that had the same structure as TABLE_A, but contained three additional fields, USERNAME, AUDIT_TIMESTAMP, OPERATION. When an insert happened, we captured the :new values (1 row in AUDIT_TABLE_A). When a delete happened, we captured the :old values (1 row in AUDIT_TABLE_A). When an update happened, we captured both the :new and :old values (2 rows in AUDIT_TABLE_A). AUDIT_TABLE_A was partitioned by day and we kept a rolling window of 30 days in AUDIT_TABLE_A.

Here is the trigger. I used scott user and emp table.
I used EMP_LOG table with additional columns ROW_TYPE ,
UPDATED_DATE, USERID .

create or replace trigger TR_EMP_BU
before update on emp
for each row
begin
INSERT INTO EMP_LOG (EMPNO,
ENAME,
JOB ,
MGR ,
HIREDATE ,
SAL ,
COMM ,
DEPTNO ,
ROW_TYPE ,
UPDATED_DATE,
USERID )
VALUES ( :OLD.EMPNO,
:OLD.ENAME,
:OLD.JOB ,
:OLD.MGR ,
:OLD.HIREDATE ,
:OLD.SAL ,
:OLD.COMM ,
:OLD.DEPTNO ,
'OLD',
SYSDATE,
USER);
INSERT INTO EMP_LOG (EMPNO,
ENAME,
JOB ,
MGR ,
HIREDATE ,
SAL ,
COMM ,
DEPTNO ,
ROW_TYPE ,
UPDATED_DATE,
USERID )
VALUES ( :NEW.EMPNO,
:NEW.ENAME,
:NEW.JOB ,
:NEW.MGR ,
:NEW.HIREDATE ,
:NEW.SAL ,
:NEW.COMM ,
:NEW.DEPTNO ,
'NEW',
SYSDATE,
USER);

end;
/

As shown you can do this with triggers, but i thought LogMiner is a utility that can give you this information; in 8.1.6.

LogMiner will not give history of changes (updates) made on the table.

Thanks guys - what I had in mind was I'd create a new table, with 2 columns for each column in the audited table, one for the old value, one for the new, rather than 2 rows , one with old & one with new values.

So here is what I came up with:

create or replace trigger tr_aud_table
before update on schema.audit_changes
for each row
begin
insert into schema.audit_changes
(ref_number,
change_date,
user,
OLD_col1,
OLD_col2,
OLD_col3)
values
(:OLD.ref_NUMBER,
SYSDATE,
USER,
OLD:col1,
OLD:col2,
OLD:col3)
UPDATE schema.AUDIT_CHANGES
SET
NEW_col1 = NEW:col1,
NEW_col2 = NEW:col2,
NEW_col3 = NEW:col3
WHERE ref_number = OLD:ref_number
END;

will this work?
does "USER" pick up the user changing the table?

thanks again

The function user returns the current user, in your case the user changing the table.

With this trigger the table is mutating, the table is affected by the triggering event. The trigger updates the table audit_changes on update of the table audit_changes.

Why do you first insert and then update the table? Now you only see the last changes made.

I think tamilselvan has given the the trigger you need.

I realized my mistake later, it should read schema.table_name - that is, referring to the table I want to audit.

Yes, I will try that trigger I believe it will fit my needs. I am still going to try one that only has one row in my audit table for each row changed in the audited table.

thanks again for your help