Solved

Copy and undo the inserted record

Posted on 2008-10-22
9
457 Views
Last Modified: 2013-12-19
Scenario
In my oracle database 2 tables T1 and T2 in the same structure. Data in the table T1 is inserted from an external application, which I don't have any control. I want to copy the data before inserting T1 to T2 and rollback the insertion in T1.

Delete operation cannot be used to T1, because if the data inserted to T1, then my application will not work.

Can anyone help me to make a solution.
0
Comment
Question by:yvijayakumar
9 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22776113
you will need a INSTEAD OF TRIGGER on the table.
http://www.psoug.org/reference/instead_of_trigger.html
0
 
LVL 35

Expert Comment

by:johnsone
ID: 22776993
From the Oracle documentation:

Restrictions on INSTEAD OF triggers:

    * INSTEAD OF triggers are valid only for views. You cannot specify an INSTEAD OF trigger on a table.
    * You can read both the :OLD and the :NEW value, but you cannot write either the :OLD or the :NEW value.


You cannot put an instead of trigger on a table, only a view.

The only way I can think of to do it is to call a procedure in a trigger on the T1 table.  The procedure would have to be an autonomous transaction.  The procedure commits, then the trigger returns an error, forcing the rollback of the original insert.
0
 
LVL 10

Expert Comment

by:dbmullen
ID: 22781397
it sounds like you want to reuse a process that you have no control over?  seems strange..  you might want to take a step back and rethink.

ALTER TABLE t1 RENAME TO t1_save ;
ALTER TABLE t2 RENAME TO t1 ;
run process
ALTER TABLE t1 RENAME TO t2 ;
ALTER TABLE t1_save RENAME TO t1 ;

0
Industry Leaders: 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!

 
LVL 35

Expert Comment

by:johnsone
ID: 22781894
I thought of this too.

You can recreate t1 as a view and then create instead of triggers on the view to direct everything where you want it to go.

alter table t1 rename to t1_table;
create view t1 as select * from t1_table;

Now you should be able to create the instead of triggers to do what you want.  You will need to handle inserts, updates and deletes with the triggers.
0
 
LVL 27

Expert Comment

by:sujith80
ID: 22784205
To me it looks like T1 has multiple data entry points and the asker wants to prevent the insertion from one of the applications.

@yvijayakumar
confirm if this is the case.

If not, why bother to have a table called T1 at all? Get rid of it and create a synonym T1 for table T2.
0
 
LVL 1

Author Comment

by:yvijayakumar
ID: 22794985
thanks to johnsone

this solution works but an exception occurs. Is there any way to avoid the exception.


CREATE TABLE T1
(
  LOGID NUMBER(10,0)
)
 
CREATE TABLE T2
(
  LOGID NUMBER(10,0)
)
 
 
 
CREATE TRIGGER Shiftdata BEFORE INSERT ON T1
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
BEGIN
ROLLPROC(:NEW.LOGID);
rollback;
END;
 
Create PROCEDURE ROLLPROC  (
LID NUMBER
)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
insert into T2 (LOGID) values (LID);
commit;
END;

Open in new window

0
 
LVL 1

Author Comment

by:yvijayakumar
ID: 22794995
Renaming of T1 is not possible because this  will crash  the external application
0
 
LVL 1

Author Comment

by:yvijayakumar
ID: 22795012
My situation is exactly the same explained by sujith80
0
 
LVL 35

Accepted Solution

by:
johnsone earned 250 total points
ID: 22795407
You cannot roll back in a trigger.  Your trigger would have to be:

CREATE TRIGGER Shiftdata BEFORE INSERT ON T1
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
BEGIN
ROLLPROC(:NEW.LOGID);
raise_application_error(-20100, 'Inserted into T2 instead of T1');
END;
/

This will cause an exception back to the application.


The only way to do this without causing an exception is the second way I suggested.  Rename T1 and put a view in its place.  Use instead of triggers to redirect DML as necessary.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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 explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

749 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