Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

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    

0
orcun_turkec
Asked:
orcun_turkec
  • 4
  • 2
1 Solution
 
orcun_turkecAuthor Commented:
I need to write logs in a different table .
0
 
slightwv (䄆 Netminder) Commented:
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
 
orcun_turkecAuthor Commented:
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
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!

 
slightwv (䄆 Netminder) Commented:
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
 
orcun_turkecAuthor Commented:
Thank you...
0
 
orcun_turkecAuthor Commented:
It works, thank you again.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now