Building an Audit Trail for Your Data - Making the Trigger Code Readable

(Page 5 of 6 )

To make the resulting trigger code somewhat readable, I’ve liberally used carriage returns CHR(10) in the print statements(a handy tool whenever dynamically generating code that might need to be debugged later). 

In Oracle, the keywords ‘:new’ and ‘:old’ allow you to reference the new value and old value, respectively, of a table column.  Some logic is required in the trigger itself to differentiate between insert, updates, and deletes.  Two variables, v_prefix and v_condition, and a simple Oracle LOOP, help us build an IF/ELSE statement.

Going back to our previous example, the build_audit_triggers script would look like the following when processing the audit_test table:


CREATE OR REPLACE TRIGGER AUDIT_TEST$AUDTRG 
 AFTER INSERT OR DELETE OR UPDATE ON AUDIT_TEST FOR EACH ROW 
DECLARE 
v_operation VARCHAR2(10) := NULL;   
BEGIN    
IF INSERTING THEN 
   v_operation := 'INS'; 
ELSIF UPDATING THEN 
   v_operation := 'UPD'; 
ELSE 
   v_operation := 'DEL'; 
END IF;     
IF INSERTING OR UPDATING THEN
  INSERT INTO AUDIT_TEST$AUD (  
  TEST_VARCHAR2,     
  TEST_CHAR,  
  TEST_LONG, 
  TEST_NUMBER, 
  TEST_NUMBER2,  
  TEST_DATE,   
  aud_action,aud_timestamp,aud_user)  VALUES 
( 
  :new.TEST_VARCHAR2,             
  :new.TEST_CHAR,       
  :new.TEST_LONG,     
  :new.TEST_NUMBER, 
  :new.TEST_NUMBER2,    
  :new.TEST_DATE,
  v_operation,SYSDATE,USER);
ELSE 
  INSERT INTO AUDIT_TEST$AUD 
( 
  TEST_VARCHAR2,   
  TEST_CHAR, 
  TEST_LONG,
  TEST_NUMBER,    
  TEST_NUMBER2,   
  TEST_DATE,    
  aud_action,aud_timestamp,aud_user)  VALUES 
(  
  :old.TEST_VARCHAR2,            
  :old.TEST_CHAR,        
  :old.TEST_LONG,    
  :old.TEST_NUMBER,             
  :old.TEST_NUMBER2,        
  :old.TEST_DATE,   
  v_operation,SYSDATE,USER);

END IF; 
END;
/



Any inserts, updates, or deletes will be captured in audit_test$aud (actual DML statements omitted):


SELECT test_varchar2,aud_action,TO_CHAR(aud_timestamp,’DD-MON-YY HH24:MI:SS’) aud_timestamp,aud_user FROM audit_test$aud;



TEST_VARCHAR2	AUD_ACTION	AUD_TIMESTAMP	AUD_USER
varchar2_data	INS	15-JAN-04 09:15:00	SCOTT
updated_data	UPD	15-JAN-04 09:16:12	SCOTT
updated_data	DEL	15-JAN-04 09:16:18	SCOTT

When displaying Oracle’s SYSDATE, you’ll need to use TO_CHAR to get the full date and time.

Next: Auditing Tips/Techniques >>
 

More Database Articles
More By Michael Kleane

Comments On: Building an Audit Trail for Your Data

>>> Be the FIRST to comment on this article!