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.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.|||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|||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;