?
Solved

Oracle: Trigger to capture table updates

Posted on 2006-06-22
2
Medium Priority
?
392 Views
Last Modified: 2008-01-09
I'd like to write a trigger that will capture updates made to a certain column of a table, and place the old and new values into another table.

Cost_Table
Product:    Watch
Price:       200

If the price of the watch is changed to 300, a row should be inserted into Cost_Table_Updates as follows:
Product:   Watch
Old_Price: 200
New_Price: 300

Thanks!
0
Comment
Question by:TotalEnergy
[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
2 Comments
 
LVL 25

Accepted Solution

by:
jrb1 earned 2000 total points
ID: 16963748
create or replace trigger
   Cost_Table_Updates
after update on cost_table
for each row

BEGIN
   IF UPDATING ('PRICE') THEN
      INSERT INTO Cost_Table_Updates (product, old_price, new_price)
         VALUES (:new.product, :old.price, :new.price);
   END IF;
END;
/
0
 
LVL 3

Expert Comment

by:hardikbeit
ID: 16966514
Trigger:

Create or Replace Trigger Update_Cost_watch
AFTER update ON cost_table --Before or after as per your requirement
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW

BEGIN
  IF UPDATING THEN
    IF :NEW.PRICE=300 THEN
        INSERT INTO Cost_Table_Updates(product, old_price, new_price)
        VALUES (:new.product_id, :Old.Price,:NEW.Price);
    END IF;
  END IF;
END;

This helps you.

Regards, Hardik
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

770 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