Re: INSERT INTO relational tables

Thanks for your hints so far.

I'm looking for both syntax simplicity and referential integrity. I'm using c++ with libpqxx, but I didn't find a good
object-relationalmapper. And I'm too lazy to implement a whole persistency layer as I don't have a huge DB with many
relations.

I tried Andreas' suggestion with a RULE for INSERT on a VIEW. I created the VIEW composing my table with customers and
orders:

CREATE VIEW vw_customer_insert AS SELECT * FROM tbl_customer, tbl_order WHERE cust_id = ord_customer;

Then I added a rule for the inserting:
CREATE RULE rl_customer_insert ASON INSERT TO vw_customer_insert DO INSTEAD ( INSERT INTO tbl_customer VALUES (DEFAULT,
new.cust_name,new.cust_address);  INSERT INTO tbl_order VALUES (NEW.ord_pieces, NEW.ord_productname, NEW.ord_price,
currval('"tbl_cusomer_cust_id_seq"'));
);

But this results in the same problem: It works fine if I insert just one new customer with one new order. But if I want
toinsert several new entries:
 

INSERT INTO vw_customer_insert(cust_name, cust_address, ord_pieces, ord_productname, ord_price)VALUES ), (‘MR. X’, ‘1st
street’,5, ‘books’, 50),  (‘MRS. Y’, ‘2nd street’, 1, ‘t-shirt’, 10);
 

This doesn't work correctly; all orders are now related to the last customer. In this example, both orders are related
toMRS. Y because I use currval() in my insert rule.
 

Do you see any solution for this? I thought that the performance of this would be better than INSERTing to
tbl_customer,fetching the ID and then do several INSERTS to tbl_order in c++. But actually I tend to do it with several
INSERTstatements in one transaction, as Steve proposed. 
 

Cheers Stefan


-- 
Psssst! Schon vom neuen GMX MultiMessenger gehört?
Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger