9i auditing via trigger

A need arose to do some column-level auditing in one of our 9i databases the other day. An example of what was implemented is as follows:

CREATE TABLE t (t1 NUMBER, t2 VARCHAR2(10));

INSERT INTO t VALUES (1, 'IAN');
COMMIT;

CREATE TABLE audit_t
(op_date DATE,
op_user VARCHAR2(255),
old_t2 VARCHAR2(10),
new_t2 VARCHAR2(10));

CREATE OR REPLACE TRIGGER audit_t_trig
BEFORE DELETE OR INSERT OR UPDATE ON t
FOR EACH ROW
BEGIN
INSERT INTO audit_t
VALUES
(sysdate,
user,
:old.t2,
:new.t2);
END;
/

So when the T2 column in table T is changed (UPDATEd, INSERTed, DELETEd) a record is written to AUDIT_T detailing the datestamp, the user, what the old value was, and what the new value is.

Cheers,

Ian