Home » SQL & PL/SQL » SQL & PL/SQL » save as.......
save as....... [message #219266] Tue, 13 February 2007 13:54 Go to next message
IT Guru
Messages: 59
Registered: January 2007
Member
using isql plus under windows

If any changes are made in eg. employees table.

Now I dont want to save these changes. But before issuing roll back command I want to save these cganes eg. new_emp table.

How to do same ?

as after adding these changes to new_emp table if issue command commit will save changes to employees table too....

not having dba privillages..

but owner of both the table
Re: save as....... [message #219270 is a reply to message #219266] Tue, 13 February 2007 14:14 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You might create a trigger which would, in an autonomous transaction, save updated records. Here is an example:
CREATE OR REPLACE TRIGGER trg_second 
  AFTER UPDATE ON first_one
  FOR EACH ROW
DECLARE
  PRAGMA autonomous_transaction;
BEGIN
  INSERT INTO second_one (b) VALUES (:NEW.a);
  COMMIT;
END;
/ 

SQL> -- first table has some records, second one is empty
SQL> select * from first_one;

         A
----------
         1
         2
         3

SQL> select * from second_one;

no rows selected

SQL> -- update first table - those changes are saved in second table
SQL> update first_One set a = 99;

3 rows updated.

SQL> rollback;

Rollback complete.

SQL> -- rollback restored previous state of first table, but changes are saved in second table
SQL> select * From first_one;

         A
----------
         1
         2
         3

SQL> select * from second_one;

         B
----------
        99
        99
        99

SQL>
Re: save as....... [message #219533 is a reply to message #219266] Wed, 14 February 2007 15:02 Go to previous message
IT Guru
Messages: 59
Registered: January 2007
Member
thanks....


Previous Topic: Group By - Top records
Next Topic: Table Design Suggestions
Goto Forum:
  


Current Time: Fri Dec 02 20:51:50 CST 2016

Total time taken to generate the page: 0.67167 seconds