Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    24

    Unanswered: Is possible of using one single statement for inserting two tables?

    Hi,

    I need to know whether it is possible of using one single SQL statemnet to implement inserting and updating operations on two tables.

    The back ground information is that the two tables are related with the following structure:

    user_group_A table
    id int -- PK,
    field1 varchar(20),
    field2 varchar(20)

    user_group_B table
    id int -- PK reference user_group_B,
    field3 varchar(20),
    field4 varchar(20)

    that is the group A has field1 and field2, while the B has field1, field2, field3, and field3.

    Thanks for you input.

    v.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Is possible of using one single statement for inserting two tables?

    Originally posted by Vernon
    Hi,

    I need to know whether it is possible of using one single SQL statemnet to implement inserting and updating operations on two tables.

    The back ground information is that the two tables are related with the following structure:

    user_group_A table
    id int -- PK,
    field1 varchar(20),
    field2 varchar(20)

    user_group_B table
    id int -- PK reference user_group_B,
    field3 varchar(20),
    field4 varchar(20)

    that is the group A has field1 and field2, while the B has field1, field2, field3, and field3.

    Thanks for you input.

    v.
    You don't say what DBMS. For Oracle, the short answer is "no".

    However, there is a way in which this could be accomplished:

    1) Create a view based on the join between A and B
    2) Create an INSTEAD OF INSERT trigger on the view, and in the trigger code perform inserts into both A and B.

  3. #3
    Join Date
    Jul 2003
    Posts
    24
    Thanks Tony for your advice.

    I prefer a genertc solution over the DB special one. I currently use PostgreSQL for this project.

    I have a look at the View. As the name indicated, it is for view only. Only a selection query can be operated on it. I also find another mechanism called rule, which can be used to insertion and update queries. I don't know whether it is a standard or not.

    Can you elaborate the second point you stated?

    Thank again.

    Vernon

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by Vernon
    Thanks Tony for your advice.

    I prefer a genertc solution over the DB special one. I currently use PostgreSQL for this project.

    I have a look at the View. As the name indicated, it is for view only. Only a selection query can be operated on it. I also find another mechanism called rule, which can be used to insertion and update queries. I don't know whether it is a standard or not.

    Can you elaborate the second point you stated?

    Thank again.

    Vernon
    My second point refers to an Oracle feature, the INSTEAD OF [INSERT/UPDATE/DELETE] trigger. It allows you to override the default action for an insert etc. on a view. For example, for a view V based on tables A and B you might define a trigger:

    CREATE OR REPLACE TRIGGER v_trg1
    INSTEAD OF INSERT ON V
    FOR EACH ROW
    BEGIN
    INSERT INTO A( id, ... ) VALUES (:NEW.id, ... );
    INSERT INTO B( id, ... ) VALUES (:NEW.id, ...);
    END;

Posting Permissions

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