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

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.

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

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

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