Solved

How Create A Before Update Trigger In Oracle

Posted on 2011-09-29
6
330 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 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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

Technology Partners: 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

Suggested Solutions

Title # Comments Views Activity
Oracle Query - Return results based on minimum value 8 59
Sybase and replication server 13 82
Dbms_job.change procedure 16 37
RMAN-06100 when restoring backupset 3 4
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

734 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