Results 1 to 5 of 5

Thread: Triger

  1. #1
    Join Date
    Aug 2003
    Posts
    2

    Unanswered: Triger

    Hi,
    I have written trigger to track the changes done on a table through web user interface.I also want to save the changed userid (webuser id).How can i pass the changed user id dynamically to the trigger.

    need help Pls ...

    Thanks
    Vijay

  2. #2
    Join Date
    Sep 2002
    Location
    Austria
    Posts
    37

    trigger ...

    a trigger is always based on a table, so a trigger can only see all those values ( :OLD, :NEW) if they are columns of the table.

    which comes back to ... the webuserid should be in the table concerned.

  3. #3
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Two ways

    Hello,

    one way is to expand the table with a dummy field and pass the webid through the insertstatement.

    INSERT INTO tab(id, text)

    in Trigger :new.text and so on
    after modification of table
    INSERT INTO tab(id, text, webid)
    in Trigger :new.webid

    another way is to pass the webid into a package, that holds the webid
    here is a package that holds your webid

    ----------------------------------------
    -- Creation of package MYPACK
    CREATE OR REPLACE
    PACKAGE PETERMA.MYPACK
    IS
    PROCEDURE setWebID(p_nWebID IN NUMBER);
    FUNCTION getWebID RETURN number;

    m_nWebID NUMBER;

    END;

    /
    -- Creation of package body MYPACK
    CREATE OR REPLACE
    PACKAGE BODY PETERMA.MYPACK
    IS
    PROCEDURE setWebID(p_nWebID IN NUMBER) IS
    BEGIN
    m_nWebID := p_nWebID;
    END;

    FUNCTION getWebID RETURN NUMBER IS
    BEGIN
    RETURN m_nWebID;
    END;
    END;

    /
    ----------------------------------------

    Before insert call
    peterma.setWebID(webid);

    in the trigger use
    peterma.getWebID to get the stored webID

    Hope that helps
    Manfred Peter
    Alligator Company Software GmbH
    http://www.alligatorsql.com

  4. #4
    Join Date
    Aug 2003
    Posts
    2

    Trigger

    Hi,
    Thanks for your prompt responses.
    But ours is very big product and i am not able to change either table or framework classes.
    Is there any way to push userid to oracle connection session so that we can access from trigger?
    If not possible please suggest a better Audit solution

    Thanks
    Vijay

  5. #5
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Lightbulb hmmm

    Hello,

    no I don´t think so.
    If you can not change the insertstaments in you framework and cannot change any object on the database, your problem is a real problem.
    Can you create views and synonyms ?


    Sorry
    Manfred Peter
    Alligator Company Software GmbH
    http://www.alligatorsql.com

Posting Permissions

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