Oracle Database Discussions

HI guyz,

I am trying to audit a table based on inserts and updates on it, I write the following trigger

CREATE TABLE emp_audit (
old_empno NUMBER(4),
old_ename VARCHAR2(10),
old_job VARCHAR2(9),
old_mgr NUMBER(4),
old_hiredate DATE,
old_sal NUMBER(7,2),
old_comm NUMBER(7,2),
old_deptno NUMBER(2),
new_empno NUMBER(4),
new_ename VARCHAR2(10),
new_job VARCHAR2(9),
new_mgr NUMBER(4),
new_hiredate DATE,
new_sal NUMBER(7,2),
new_comm NUMBER(7,2),
new_deptno NUMBER(2),
changed_by VARCHAR2(8),
change_type CHAR(1),
timestamp DATE );

CREATE OR REPLACE TRIGGER LogEmpChanges
BEFORE INSERT OR DELETE OR UPDATE ON scott.emp
FOR EACH ROW
DECLARE
v_ChangeType CHAR(1);
BEGIN
/* Use 'I' for an INSERT, 'D' for DELETE, and 'U' for UPDATE. */
IF INSERTING THEN
v_ChangeType := 'I';
ELSIF UPDATING THEN
v_ChangeType := 'U';
ELSE
v_ChangeType := 'D';
END IF;

/* Record all the changes made to scott.emp in
emp_audit. Use SYSDATE to generate the timestamp, and
USER to return the userid of the current user. */
INSERT INTO emp_audit
(change_type, changed_by, timestamp, old_empno,
old_ename, old_job, old_mgr, old_hiredate, old_sal,
old_comm, old_deptno, new_empno, new_ename, new_job,
new_mgr, new_hiredate, new_sal, new_comm, new_deptno)
VALUES
(v_ChangeType, USER, SYSDATE, :old.empno, :old.ename,
:old.job, :old.mgr, :old.hiredate, :old.sal, :old.comm,
:old.deptno, :new.empno, :new.ename, :new.job, :new.mgr,
:new.hiredate, :new.sal, :new.comm, :new.deptno);
END LogEmpChanges;
/

I need to get the OS user as well, as fetching the "USER" will not help because there is only one application user which is connecting to the database, we can trap the user with the "OSUSER" that shows in the "V$SESSION" view.

PLEASE give some suggestions...