Oracle Database Discussions

Hi Friends,
I'm not able to capture the USER_D of the user who deletes some record while implementing auditing through triggers


the structure of the Audit Table is as follows :

AUDIT_ID NUMBER(15,0)
TABLE_NAME VARCHAR2(30 BYTE)
COLUMN_NAME VARCHAR2(30 BYTE)
PRI_KEY_VALUE NUMBER(15,0)
AUDIT_TYPE VARCHAR2(10 BYTE)
BY_USER NUMBER(15,0)
AUDIT_TIMESTAMP TIMESTAMP(6)
OLD_VALUE VARCHAR2(100 BYTE)
NEW_VALUE VARCHAR2(100 BYTE)
COMMENTS VARCHAR2(50 BYTE)

...of which in the BY_USER field, while deleting some record, I want to insert the USER_ID of the user who is deleting the record, which I
don't know how to capture.

In every table I wish to audit, I've added fields like...

CREATED_BY NUMBER(15,0)
CREATED_DATE TIMESTAMP(6)
UPDATED_BY NUMBER(15,0)
UPDATED_DATE TIMESTAMP(6)

the above fields are inserted and updated whenever a record is created or updated (BUT Deletion of a record is not captured anywhere)

The trigger looks like this...


create or replace TRIGGER TRG_USAGE_PROFILE
AFTER INSERT OR UPDATE
ON S_USAGE_PROFILE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
ln_Table_Name VARCHAR2(100) :='S_USAGE_PROFILE';
BEGIN
NULL;

IF INSERTING
THEN

PKG_AUDIT.PRC_AUDIT_TRAIL
( ln_Table_Name,
'USAGE_PROFILE_NAME',
:NEW.USAGE_PROFILE_ID,
'INSERT',
:NEW.CREATED_BY,
CURRENT_TIMESTAMP,
'-1',
:NEW.USAGE_PROFILE_NAME,
NULL
);

PKG_AUDIT.PRC_AUDIT_TRAIL
( ln_Table_Name,
'USAGE_PROFILE_DESC',
:NEW.USAGE_PROFILE_ID,
'INSERT',
:NEW.CREATED_BY,
CURRENT_TIMESTAMP,
'-1',
:NEW.USAGE_PROFILE_DESC,
NULL
);

ELSIF UPDATING
THEN

IF (NVL(:OLD.USAGE_PROFILE_NAME,0) <> NVL(:NEW.USAGE_PROFILE_NAME,0))
THEN
PKG_AUDIT.PRC_AUDIT_TRAIL
( ln_Table_Name,
'USAGE_PROFILE_NAME',
:NEW.USAGE_PROFILE_ID,
'UPDATE',
:NEW.UPDATED_BY,
CURRENT_TIMESTAMP,
:OLD.USAGE_PROFILE_NAME,
:NEW.USAGE_PROFILE_NAME,
NULL
);
END IF;

IF (NVL(:OLD.USAGE_PROFILE_DESC,0) <> NVL(:NEW.USAGE_PROFILE_DESC,0))
THEN
PKG_AUDIT.PRC_AUDIT_TRAIL
( ln_Table_Name,
'USAGE_PROFILE_DESC',
:NEW.USAGE_PROFILE_ID,
'UPDATE',
:NEW.UPDATED_BY,
CURRENT_TIMESTAMP,
:OLD.USAGE_PROFILE_DESC,
:NEW.USAGE_PROFILE_DESC,
NULL
);
END IF;



END IF;

END TRG_USAGE_PROFILE;

above trigger does well when a record is added or updated.

Please tell me how to capture the USER ID of the use who is deleting the record?

Thanks,
Mukesh