Solved

Copy and undo the inserted record

Posted on 2008-10-22
9
453 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.

Join & Write a Comment

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 shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

747 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

13 Experts available now in Live!

Get 1:1 Help Now