Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2004
    Posts
    127

    Unanswered: inserting values into multiple tables

    hi can anyone advise me how to insert a value into a table in which it is automatically inserted into a second table. So if i insert value 1 into col1 of table A the same value will will automatically be inserted into, say col1 of table B.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You could use a database trigger (AFTER INSERT in your example).

  3. #3
    Join Date
    Oct 2004
    Posts
    145
    Trigger would work but I would serious look at your database design, if you have to insert into two different table for a same value.

  4. #4
    Join Date
    Jun 2004
    Posts
    127
    are triggers easy to use, can u advise where i can attain info on creating triggers?

    Reason why im inserting into two different tables for a same value is because im O2R mapping, and due to the impedance mismatch, i have to insert structural tables into my design.

  5. #5
    Join Date
    Jan 2004
    Posts
    492
    Everything you ever wanted to know about triggers straight from the horses mouth. Triggers are easy, but do add overhead - make sure to keep that in mind.

    http://download-west.oracle.com/docs...g13trg.htm#376
    Oracle OCPI (Certified Practicing Idiot)

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >im O2R mapping
    What is "O2R mapping" & why does it matter?

    >the impedance mismatch
    Impedence mismatch is a term involving electrical/electronic design.
    What does it have to do with RDBMS?
    Why does it matter to either the problem or envisioned solution?

    >insert structural tables into my design.
    Compare & contrast a "structural" table vs. any other type of table.

    What problem are you really trying to solve?
    What's preventing you from using a 2nd INSERT statement immediately after the 1st INSERT and only COMMIT if there is no error on either?

    I'll agree with Jim Yoo. The "design" or lack thereof needs to be improved to avoid this nonsense.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jun 2004
    Posts
    127
    O2R is my shorthand for describing object-to-relational mapping, in my case im mapping UML based object classes into the RM. Ive adopted the equation from date & darwin that: an object class = a domain, opposed to using a class=table approach, an approach which they decline.

    due to the mismatch in paradigms between the two, i have to insert certain 'structural' tables into my design to accomodate the mapping process - this is unavoidable as im mapping extremely complex metadata structures.

    im using an apprach which utilises type inheritance, so that conceptually, i am able to preserve the OO structure, whilst mapping to the [O]RDBMS. heres an instance of my problem:

    I have type A, it has a composite association to TYPE Z (i.e. Z owns A). type Z is a parent of types B & C.

    [Type Z]<>----------------------[Type A]
    .....|
    .... /.\
    ..../....\
    ../.......\
    [type B] [Type C]


    i create a table of type A (i.e. CREATE TABLE A OF A), and a table of B, and of C and of Z. each have an attribute ID. table A also contains an attribute Z_ID - for the link to Z. other attributes for B & C have been omitted.

    if i insert into Z, then i must insert either into B, or C (depending on which is used during that particular instance), and this is where my problem lies, as i have to insert the same value into either Z & B simultaniously, or Z & C. what i'd really like is to keep Z as a type, and have the ability to insert Z_ID directly into tables B or C in order to minimise the number of tables used.

    i cannot use: a foreign key: table A REFERENCES table B [or C] ON DELETE CASCADE.

    so a trigger looks like the likely choice. is there any other possible solutions. such as using a function maybe? - only i have little knowledge on this area.

    thanks for sparing time to read this thread.
    Last edited by FAC51; 12-30-04 at 11:37.

  8. #8
    Join Date
    Oct 2004
    Posts
    145
    Unless you are already working with a DBA, I would very strongly suggest you work with a DBA. Two of you may be able to come up with a design that meets your requirement without havnig double inserts or decide on an appropriate trigger.

    I have seen some Object Oriented application fail due to lack of communication between developer and DBA. I also have seen a succesful implementation of this as well. (Oracle and Toplink [prior to purchase of toplink by oracle]).

  9. #9
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    I'm not clear when you are referring to object types and when you are referring to tables (particularly since you used the same names A, B, C and Z for both).

    I also don't understand what you mean by "I have type A, it has a composite association to TYPE Z (i.e. Z owns A)", illustrated by the "<>---" symbol in your diagram. How does a type "own" anything? Is Type A a subtype of Type Z? Or is Table Z the parent of Table A via a foreign key relationship? Or both?

    An explanation of what is meant 'structural' tables might also help. All we have so far is that they are something to do with a mapping process of some sort.

    If Types B and C are subtypes of Z, then why can't you create one table of Type Z and place B, C and Z objects in it? For example, if Employee and Client are both subtypes of Person, you could CREATE TABLE PEOPLE OF PERSON and store both Employees and Clients in it.

    Or perhaps better still, implement a relational design and add a set of object views which present the same data as complex objects.

    For my 2 cents, I would say that trying to duplicate data via triggers will
    1. create scalability problems as volumes rise
    2. multiply the complexity (time, cost, headaches) of any future work on the system
    3. inevitably go out of sync sooner or later.

  10. #10
    Join Date
    Jun 2004
    Posts
    127
    hi william,

    when i say object types, i mean UDT's but oracle creates only types as objects i.e CREATE TYPE X AS OBJECT, so i guess they're object types

    in the metamodel im mapping from, column classes are classed as features of classifiers. a classifier owns a feature via composition. a classifier being either: a table, schema, catalog, view, etc. so a column is a feature of table for example. So when i create a column instance, it must either relate to a view or table for example. & thats why i have to implement a kind of structural table (say called X) so that i can go from column to X then to either view or table depending on context. i cant implement a FK to REF either view or table.

    with regard to :

    "I also don't understand what you mean by "I have type A, it has a composite association to TYPE Z (i.e. Z owns A)", illustrated by the "<>---" symbol in your diagram."

    that is a mistake on my behalf, i mean to say object class A in UML has a composite asso to object class Z, its just that ive made the conversion to UDT's.

    when i say structural table, it is a table which i have to implement into my design to preserve the semantics of the OO design once mapped into the RM.


    "If Types B and C are subtypes of Z, then why can't you create one table of Type Z and place B, C and Z objects in it? For example, if Employee and Client are both subtypes of Person, you could CREATE TABLE PEOPLE OF PERSON and store both Employees and Clients in it."

    this is similar to the way my design is built for certain aspects. only the 'Z' appears at such a high level in the design, it would be like the equivalent of amalgamating 20+ classes into a single table which would not be possible for what im doing.

    with regard to triggers, its the only solution that so far, i can see will work, even though ive heard a mixed bag of opinions about them. until i work out a better way it looks like im gonna have to endure a series of trade offs with triggers.

  11. #11
    Join Date
    Nov 2004
    Location
    Temple University
    Posts
    36
    Sounds to me like 2nd Normal Form tables...
    E.F. Codd should beat you over the head with a 5th normal form....

Posting Permissions

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