Solved

Copy and undo the inserted record

Posted on 2008-10-22
9
458 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
[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
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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

734 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