Home » SQL & PL/SQL » SQL & PL/SQL » Backup before Update
Backup before Update [message #225547] Tue, 20 March 2007 05:23 Go to next message
vishal gupta
Messages: 111
Registered: March 2001
Senior Member
Hi,
I have a update statement which updates 4 columns of a row.
I want to back up (save in another table) the original values of the columns which are going to be updated before updation.

Can it be done without using triggers ?
I am using 9i.



regards
Re: Backup before Update [message #225550 is a reply to message #225547] Tue, 20 March 2007 05:30 Go to previous messageGo to next message
Littlefoot
Messages: 20893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
One way would be this: you, of course, know which records are to be updated. This condition is to be part of the WHERE clause. Let's say, for example, that you'll modify records of employees who work in department 10. So, first save those records:
CREATE TABLE save_emps AS
  SELECT * FROM employees
  WHERE dept_number = 10;
Now update those records, using the same WHERE clause:
UPDATE employees SET
  salary = salary * 1.5,
  bonus = 1000
WHERE dept_number = 10;
Re: Backup before Update [message #225558 is a reply to message #225547] Tue, 20 March 2007 06:09 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
I don't think there is a sql-statement which does that in one step.
So you have to do it yourself in script or pl/sql.

the logic could be:
1) copy "to be altered" rows to the "history" table
2) update the original table

Re: Backup before Update [message #225564 is a reply to message #225547] Tue, 20 March 2007 06:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What's wrong with triggers? This is just the sort of thing they they're there for.

I can't think of another way of doing it, although it might be possible with an Update-join view.
Re: Backup before Update [message #225565 is a reply to message #225564] Tue, 20 March 2007 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

An update statement can only update one table even when using an updatable view.

Regards
Michel
Re: Backup before Update [message #225641 is a reply to message #225565] Tue, 20 March 2007 12:29 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Not if you have an instead-of trigger on your view..
But I agree with JRowbottom: why? What is the point?
Previous Topic: how to use index on function based value
Next Topic: Getting error in procedure
Goto Forum:
  


Current Time: Mon Dec 05 06:37:43 CST 2016

Total time taken to generate the page: 0.08461 seconds