DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Trigger to store user(actual) details

  1. #1
    Join Date
    Jun 2005
    Posts
    20

    Trigger to store user(actual) details

    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.

  2. #2
    Join Date
    May 2003
    Location
    Pretoria, Rep of South Africa
    Posts
    191
    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 !!!
    Able was I ere I saw Elba

  3. #3
    Join Date
    Jun 2005
    Posts
    20
    Hi Robert
    tks for response.
    can you give an example with complete details. i don't have an exp. on this

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    tahiti.oracle.com has loads of examples

  5. #5
    Join Date
    Jun 2005
    Posts
    20
    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

  6. #6
    Join Date
    Mar 2004
    Location
    India
    Posts
    72
    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.

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    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?

  8. #8
    Join Date
    Mar 2004
    Location
    India
    Posts
    72
    Quote Originally Posted by davey23uk
    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 !!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width