Oracle’s built-in audit capability stops at the table level. That is, you can audit who has accessed a table, but not which row was accessed. However, Oracle has provided a mechanism that lets you build your own row-level audit facility. Through the use of after-insert, after-update, or after-delete triggers, you can capture this information yourself and write it out to a separate table. The following trigger is an example of an update trigger.

If you need to audit SELECT access to an individual row, you will have to do this from within your application. Here is an example trigger used to audit row-level inserts, updates, and deletes:

CREATE OR REPLACE TRIGGER log_actions                        
AFTER INSERT OR UPDATE OR DELETE                
ON generic_table                
FOR EACH ROW          
DECLARE               
   action_id char(1); 
   event_id int;  
   table_key varchar(80);                       
   timestamp date;    
   table_name varchar(30);                      
   username varchar(30);                        
BEGIN                 
   SELECT event_seq.nextval INTO event_id FROM dual;              
   SELECT sysdate INTO timestamp FROM dual;     
   SELECT user INTO username FROM dual;         
   table_name := 'generic_table;                  
   IF DELETING THEN   
        action_id := 'D';                       
        table_key := to_char(generic_table.primary_key);    
   END IF;            
   IF UPDATING THEN   
        action_id := 'U';                       
        table_key := to_char(generic_table.primary_key);    
   END IF;            
   IF INSERTING THEN  
        action_id := 'I';                       
        table_key := TO_CHAR(generic_table.primary_key);    
   END IF;   

rem
rem   The debugging lines can be left in. They will not have an effect
rem   unless serveroutput is turned on in SQL*Plus 
rem        
        dbms_output.enable(10000);              
        dbms_output.put_line('tablename '||table_name);                   
        dbms_output.put_line('actionid  '|| action_id);                    
        dbms_output.put_line('username  '|| username);                     
        dbms_output.put_line('tablekey  '|| table_key);                    
        dbms_output.put_line('eventid   '|| event_cdr_id);                 
        dbms_output.put_line('timestamp '|| timestamp);                    
 
   INSERT INTO event_table_audit_t (event_id,
                                    timestamp,                     
                                    table_name,                       
                                    table_key,                        
                                    action)                           
                            VALUES (event_id, 
                                    timestamp,                    
                                    table_name,                       
                                    table_key,                        
                                    action_id);                       
END;                  
/

This example is based upon a production row-level auditing trigger. In this case, an audit trail record is written for every insert, update, and delete. If you use this example, please remember to replace the placeholders generic_table and primary_key with your actual table name and the columns in your primary key. We are also assuming that the primary key itself will never be updated.

A useful variant of this trigger would be to grab the old and new values for the columns being updated and to write those out to a table. This would allow you to know not only which row was changed, but also which columns and values were changed.