jknj72
asked on
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...
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...
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.
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.
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
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
ASKER
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!
ASKER
Where ... as in where is the Insert being called . I know grammar not good ;)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
that's what I meant. Ok I will try it tomorrow .. Thanks
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$USERSASKER
I used SyS_CONTEXT with user and host with ENVIRON(?). I can post actual code tomorrow
If "where" means something else, what is it?