Solved

How Create A Before Update Trigger In Oracle

Posted on 2011-09-29
6
327 Views
Last Modified: 2012-05-12
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    

0
Comment
Question by:orcun_turkec
  • 4
  • 2
6 Comments
 

Author Comment

by:orcun_turkec
ID: 36814328
I need to write logs in a different table .
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 36814356
The docs have everything you need but try this.
drop table x purge;
create table x(y char(1));

insert into x values('a');
commit;

drop table log purge;
create table log(old_y char(1));

create or replace trigger x_trig
before update of y on x
for each row
begin
	insert into log values(:old.y);
end;
/

update x set y='z';
select * from log;

Open in new window

0
 

Author Comment

by:orcun_turkec
ID: 36814584
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.    
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36814628
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.
0
 

Author Comment

by:orcun_turkec
ID: 36814736
Thank you...
0
 

Author Closing Comment

by:orcun_turkec
ID: 36884433
It works, thank you again.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now