Trigger to store user(actual) details

[balasmg]

Hi All,

Our application behave such that, we have to mention schema username and password before build the application, hence we are having a common schema for all developer. pl. note this constraint
(EXAMPLE : USERNAME / PASSWORD : DEV/DEV)
I need to track who is doing modification as INSERT/UPDATE/DELETE in some important tables.
I have created column in each such table with created_user, created_date, modified_user, modified_date.
i have written trigger as

CREATE OR REPLACE TRIGGER bi_bu_emp
BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW
BEGIN
IF INSERTING THEN
SELECT USER, SYSDATE INTO :NEW.created_user, :NEW.created_time FROM DUAL;
ELSIF UPDATING THEN
SELECT USER, SYSDATE INTO :NEW.modified_user, :NEW.modified_time FROM DUAL;
END IF;
END;

I AM NOT ABLE TO TRACE WHOSE HAS PARTICULARY DONE THE MODIFICATION, AS ALL THE DEVLOPER ARE USING
SAME SCHEMA, AND "USER" FUNCTION WILL RETURN RESULT AS DEV.

I NEED TO KNOW WHICH USER HAS DONE THIS. IS THER ANY WAY TO GET THE USER INFORMATION.
IN OTHER SITUVATION. TO KILL PARTICULAR SESSION WHICH HANGS UP, I AM USING V$SESSION TABLE, WHICH HAS COLUMN "MACHINE" WHICH IDENTIFIES, WHICH USER SESSION NEED TO BE KILLED. IS THERE ANYWAY TO USE THIS OR ANY OTHER OPTION TO TRACK THE USER.

PL. LET ME KNOW.

Our application details:

Database details: oracle 9i
front end : java.

[robertbalmer]

Have you considered using Auditing. I assume you are in a dev environment that shouldnt use to much resources.

You can activate select,update,delete,insert on any dev table and it is possible to retrieve the os user if you join the session info with audit info.

I recently wrote a procedure/job to retrieve such info to track the developers changing metadata and what they have changed....and they are red-faced !!!

[balasmg]

Hi Robert
tks for response.
can you give an example with complete details. i don't have an exp. on this

[davey23uk]

tahiti.oracle.com has loads of examples

[balasmg]

tks for response.
what about by using session details as like below

CREATE OR REPLACE TRIGGER bi_bu_emp
BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW
BEGIN
IF INSERTING THEN
SELECT SYSDATE INTO :NEW.created_date FROM DUAL;
SELECT substR(machine,1,40) INTO :NEW.created_user FROM v$session WHERE sid = (SELECT sid FROM v$mystat WHERE rownum = 1);
ELSIF UPDATING THEN
SELECT SYSDATE INTO :NEW.modified_date FROM DUAL;
SELECT machine INTO :NEW.modified_user FROM v$session WHERE sid = (SELECT sid FROM v$mystat WHERE rownum = 1);
END IF;
END;

i granted the dba role to the dev schema.
I am getting result when i run the statement in sqlplus,
SELECT substR(machine,1,40) FROM v$session WHERE sid = (SELECT sid FROM v$mystat WHERE rownum = 1);

but when i run the same in trigger it give compilation error as

-------- ---------------------------------------------------------------
3/1 PL/SQL: SQL Statement ignored
3/94 PL/SQL: ORA-00942: table or view does not exist

pl. let me know what need to be done

[Sathy]

Should be a grant problem..

also

Same statements are there in Inserting & Updating.. U can write as

If inserting or updating
.
. Statements......
.
End if;

Or

U can omit that if statement(In this case), bcoz, trigger definition itself contains the same.

[davey23uk]

wish you would use full words, makes your posts really hard to read.

Anyway, you need access to v$session directly, not through a role - grant it to yourself

You do realise that your trigger doesnt actually do anything dont you - it just selects the information?

[Sathy]

You do realise that your trigger doesnt actually do anything dont you - it just selects the information?

It is "Before" event trigger. The select statement assigns values into :New.Field_Name. It will be stored in table know !!!