Solved

Copy and undo the inserted record

Posted on 2008-10-22
9
454 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 142

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 34

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
 
LVL 34

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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ā€¦
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now