Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How Create A Before Update Trigger In Oracle

Posted on 2011-09-29
6
Medium Priority
?
337 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 

Author Comment

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

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 77

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.

705 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