Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Copy and undo the inserted record

Posted on 2008-10-22
9
Medium Priority
?
461 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 750 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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…
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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
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
Suggested Courses

564 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