How Create A Before Update Trigger In Oracle
Hi experts,
I need to create a before update trigger in Oracle.
I have a table X on which the trigger will run. If column Y of table X is updated
I want to write it in logs before being updated. How can I do that ?
Thanks,
Orçun
I need to create a before update trigger in Oracle.
I have a table X on which the trigger will run. If column Y of table X is updated
I want to write it in logs before being updated. How can I do that ?
Thanks,
Orçun
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you slightwv ;
In the Log table I also need to see the User which updated X table, update date , old value of
Y column and new value of Y column.
In the Log table I also need to see the User which updated X table, update date , old value of
Y column and new value of Y column.
You can add whatever additional information you want.
the 'new' value is :new.y
Update date is just: sysdate
How to get the 'user' depends a lot on your system. If these are users that log into the database you can use sys_context:
http://psoug.org/reference/sys_context.html
sys_context('USERENV', 'SESSION_USER')
maybe the OS username:
sys_context('USERENV', 'OS_USER')
If this is an application that uses a common database user like a web app of some type, the problem gets a little more difficult.
the 'new' value is :new.y
Update date is just: sysdate
How to get the 'user' depends a lot on your system. If these are users that log into the database you can use sys_context:
http://psoug.org/reference/sys_context.html
sys_context('USERENV', 'SESSION_USER')
maybe the OS username:
sys_context('USERENV', 'OS_USER')
If this is an application that uses a common database user like a web app of some type, the problem gets a little more difficult.
ASKER
Thank you...
ASKER
It works, thank you again.
ASKER