Trigger on Table to logg user info

I have to create a trigger to get where the Insert into a table happened from. There are many places that can Insert into my table LCF_CHECK_TABLE.
I want to be able to log a bad LCF Name(I already have this functionality) into this table from any number of our processes. I have a Create_By field and a Create_Date field that are defaulted to USER and SYSTIMESTAMP. I couldn't find anything that I can put as a default for CREATED_FROM field that will tell me where the Insert, into the table, came from. I have some code that uses the v$session table that has some of the values I want. If I could, Id like the username,machine and maybe a couple of more fields that would help me and I will save them to my field CREATED_FROM. If I could do this as a default for my field that would be ideal but if not then I will just create the trigger.
I essentially want to who what and where in my table so we can look back and have this info at our finger tips ....Anybody know how I can do this? Im thinking  that maybe the v$session is not the table I need because its giving me all the session info and I just want info pertaining to a specific program, user and or machine?
Thanks in advance...
jknj72Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Where as IP Address or client machine?  Remember the SYS_CONTEXT I mentioned in your previous question?  That would be the function you need.

If "where" means something else, what is it?
0
johnsoneSenior Oracle DBACommented:
To get the information, look at SYS_CONTEXT.  That is better than selecting from V$SESSION.

The documentation for the function is here -> http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions184.htm#SQLRF06117

If what you are looking for isn't there, let us know specifically what you are looking for and we'll see if we can find it.
0
MikeOM_DBACommented:
Perhaps you are looking to something similar to an "audit trail"?
Check out the AUDIT statement and if its not enough, then you would have to create your own audit table with corresponding triggers on the tables to be audited.
:p
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jknj72Author Commented:
Where as in from where is the insert happening. The program and client machine would be nice . I will look closer into the SYS_CONTEXT, I was hoping to find a default I could set but the Trigger will work too. Will let you know about the results tomorrow. Thanks all!
0
jknj72Author Commented:
Where ... as in where is the Insert being called . I know grammar not good ;)
0
slightwv (䄆 Netminder) Commented:
>> I was hoping to find a default

SYS_CONTEXT is a function and so is SYSDATE...  There really are no 'defaults' per say.  Just calls you make to retrieve values.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jknj72Author Commented:
that's what I meant. Ok I will try it tomorrow .. Thanks
0
MikeOM_DBACommented:
Where as in from where is the insert happening

If you are using an application server ( like websphere, weblogic, tomcat, tuxedo, OAS, etc...) and the application connections login with the same account, the only information you will get is the source machine from which the application server is connecting.
An Example would look like this:
DB User Name		APPUSER <= app server connects w/this user
OS Process ID		31064288
OS User Name		root
OS Process ID		1234
Host			webnode4
Terminal		unknown
Current Client ID	Unavailable
Current Client Info	Unavailable
Program			JDBC Thin Client
Service			SYS$USERS

Open in new window

0
jknj72Author Commented:
I used SyS_CONTEXT with user and host with ENVIRON(?). I can post actual code tomorrow
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.