Solved

How Create A Before Update Trigger In Oracle

Posted on 2011-09-29
6
326 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.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

706 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

18 Experts available now in Live!

Get 1:1 Help Now