Received: from mail01.phoenixdsl.com (mail.phoenixdsl.com [216.178.151.24]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f01AK2L54985 for ; Mon, 1 Jan 2001 05:20:02 -0500 (EST) (envelope-from mdavis@sevainc.com) Received: (qmail 1217 invoked by uid 89); 1 Jan 2001 10:19:44 -0000 Received: from unknown (HELO SALLY) (64.32.157.45) by 0 with SMTP; 1 Jan 2001 10:19:44 -0000 Received: by localhost with Microsoft MAPI; Mon, 1 Jan 2001 03:19:41 -0700 Message-ID: <01C073A1.ADE7EA80.mdavis@sevainc.com> From: Michael Davis Reply-To: "mdavis@sevainc.com" To: PostgreSQL-SQL Subject: Updating two table via a Rule? Date: Mon, 1 Jan 2001 03:19:37 -0700 Organization: Seva Inc. X-Mailer: Microsoft Internet E-mail/MAPI - 8.0.0.4211 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Archive-Number: 200101/1 X-Sequence-Number: 662 How do I create a rule for a view that inserts into two tables? I have a view based on two tables. I would like to create insert, update, and delete rules for this view to: - update both tables when the view is updated - delete from both tables when a record is deleted from the view - insert into both table when a record is inserted into the view Here is the view: CREATE VIEW reg_PaymentLines AS SELECT P.MemberID, P.PaymentsID, P.PaymentDate, PL.PaymentLineID, PL.PaymentTypesID, PL.Amount FROM Payments P, PaymentLines PL WHERE P.PaymentsID = PL.PaymentsID; I have tried to create two insert rules on the view as follows: CREATE RULE reg_PaymentLines_r1 AS ON INSERT TO reg_PaymentLines DO INSTEAD INSERT INTO PaymentLines (PaymentsID, PaymentLineID, Amount) VALUES (new.PaymentsID, new.PaymentLineID, new.Amount); CREATE RULE reg_PaymentLines_r2 AS ON INSERT TO reg_PaymentLines DO INSTEAD INSERT INTO Payments (MemberID, PaymentsID, PaymentDate, Amount) VALUES (new.MemberID, new.PaymentsID, new.PaymentDate, new.Amount); PostgreSQL allows me to create the two rules. However, when I insert into the view, I get a foreign key constraint violation because the PaymentID does not exist in the Payments table. There is a foreign key constraint from PaymentLines.PaymentsID to Payments.PaymentsID. It appears that either: - the insert into the PaymentLines table before the insert occurs in the Payments tables - or that the insert into the PaymentLines table is not aware of the insert into the Payments table. - or that the insert to the Payments table is being ignored I get the same error regardless of how the two rules are created (i.e. same error if rule #2 is applied before rule #1). Any suggestions on how to get this to work? Any help is greatly appreciated. Thanks, Michael Davis