Results 1 to 8 of 8

Thread: Mapping...

  1. #1
    Join Date
    Oct 2003
    Location
    Mumbai,India.
    Posts
    36

    Unanswered: Mapping...

    Dear Friends,

    Is there a way to map tables in Oracle 9i R2 running on Windows?

    Table 1
    --------------------
    A - B - C
    ---------------------
    A1 B1 C1
    A2 B2 C2
    A3 B3 C3
    ----------------------
    Now this table content has to be mapped into two tables.
    as,
    Table 2:
    -----------
    A - C
    -----------
    A1 C1
    A2 C2
    A3 C3
    -----------

    Table 3;
    -----------
    B - C
    -----------
    B1 C1
    B2 C2
    B3 C3
    -----------

    After these table creations ,if we modify any of these table means that modification should get reflected on other tables also.

    Please shed some light, pls
    Thanks in advance for your precious time and efforts.

  2. #2
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    1. Create table TABLE_2 as select a, c from TABLE_1
    2. Create table TABLE_3 as select a, b from TABLE_1
    3. Create triggers on TABLE_1 that propagates modifications

    HOWEVER

    May I suggest NOT TO CREATE tables, but just create VIEWS...

  3. #3
    Join Date
    Oct 2003
    Location
    Mumbai,India.
    Posts
    36
    Hi Thanks for your reply..
    How to write a trigger for this to probagate modifications
    some simple example Please....

  4. #4
    Join Date
    Oct 2003
    Location
    St.Louis,MO
    Posts
    120
    As stated before it would be much easier to just create views.
    This would eliminate the need for triggers.
    Use the same SQL as you would for creating tables only replace create table with create view.

  5. #5
    Join Date
    Sep 2003
    Location
    NE Florida w/ view of co-workers
    Posts
    32
    Use views, definitely easier and no programming bugs to worry about.

  6. #6
    Join Date
    Oct 2003
    Location
    Mumbai,India.
    Posts
    36
    Thanks Views O.K..
    but is there any possibility to adopt this for creating table?
    please...

  7. #7
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Just an example to get you started...

    CREATE OR REPLACE TRIGGER TAI_TABLE1
    AFTER INSERT
    ON TABLE1
    FOR EACH ROW
    begin
    insert into table2 (a,c) values (:new.a, :new.b);
    end;

    CREATE OR REPLACE TRIGGER TAD_TABLE1
    AFTER DELETE
    ON TABLE1
    FOR EACH ROW
    begin
    delete from table2 where a = ld.a and c = ld.c;
    end;

    CREATE OR REPLACE TRIGGER TAU_TABLE1
    AFTER UPDATE
    ON TABLE1
    FOR EACH ROW
    begin
    update table2
    set a = :new.a, b = :new.b
    where a = ld.a and c = ld.c;
    end;

  8. #8
    Join Date
    Oct 2003
    Location
    Mumbai,India.
    Posts
    36
    Thank you very much.....
    thanks for your time and efforts

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •