1 2 3 Previous Next 31 Replies Latest reply on Nov 30, 2012 3:39 PM by JustinCave Go to original post
      • 30. Re: updating the data from one schema to another schema
        If you really want a help just post Create and Insert statements for SCHEMA1.EMP table and SCHEMA2.T1,SCHEMA2.T2,SCHEMA2.T3 and SCHEMA2.T4 and results you are expecting. Please paste your SQL code between
        • 31. Re: updating the data from one schema to another schema
          This is a very, very poor way of doing things. It is a bad design. The business requirements that lead to this design are, apparently, unknown. If we knew the business requirements, we could undoubtedly come up with a better design. If you really, really, really want to implement a poor design, however, you can do something like
          CREATE OR REPLACE TRIGGER trg_synch
            AFTER INSERT OR UPDATE OR DELETE ON source_schema.source_table
            FOR EACH ROW
            IF( inserting )
              INSERT INTO destination_schema.destination_table( col1, col2, col3 )
                VALUES( :new.col1, :new.col2, :new.col3 );
            ELSIF( updating )
              -- Assuming col1 is the key
              UPDATE destination_schema.destination_table
                 SET col2 = :new.col2,
                     col3 = :new.col3
                WHERE col1 = :new.col1; 
            ELSIF( deleting )
              DELETE FROM destination_schema.destination_table
               WHERE col1 = :old.col1;
            END IF;
          Of course, you would need a similar trigger on the destination table if you want changes there to be copied to the source table. And if you're going to implement that sort of recursive relationship, you would need to add additional logic to both triggers to prevent an infinite loop.

          As has been mentioned, this violates basic normalization and is a bad design. It will undoubtedly cause you various problems down the line because it is not a particularly maintainable or performant solution.

          1 2 3 Previous Next