Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2006
    Posts
    9

    Smile Unanswered: Help needed for BEFORE DELETE Trigger

    Here is the scenario:

    I have a web application. Users need to login to access this application. Oracle 10g is the back end database for this application.

    Application User -> Joe
    Database schema owner -> focal_prod

    Joe logged into the web application and trying to delete an employee from EMP table. I need to audit this action and create a record in EMP_AUDIT table. I need to keep Joe (application user) name in this record under DELETED_BY column. How do I pass Joe name to the database trigger?

    Thanks in advance,
    Rao

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    research APPLICATION_INFO
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You could also do something like this in the trigger ... you would have to grant "select" priv on v$_process and v$_session

    SELECT S.Username, S.Machine, S.PROGRAM
    INTO v_username, v_machine, V_PROGRAM
    from V$PROCESS P, V$SESSION S
    where P.Addr = S.Paddr
    and S.Audsid = USERENV('SESSIONID');

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    In 10g, you can use

    Code:
    DBMS_SESSION.SET_IDENTIFIER('JOE');
    just prior to every change, which updates the value of V$SESSION.CLIENT_IDENTIFIER to the user's name. Then you can point to it from a TRIGGER. You can do somewhat the same by using a PACKAGE SPEC variable as a global variable to hold this type of information.

    The key, coming in from a web application using connection pooling, is setting these variables with the exact same connection which executes the subsequent SQL, in serial order. If you can't reliably do that, then you may be better off adding a MOD_ID column in your EMP table, where you can pass 'JOE' in for INSERTs and UPDATEs, and during DELETEs, use that same name in your EMP_AUDIT table.

    ---=cf

Posting Permissions

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