X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (mx2.hub.org [200.46.204.254]) by postgresql.org (Postfix) with ESMTP id 2CD7E11F6B76 for ; Tue, 18 Apr 2006 15:19:26 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: -1.248 X-Spam-Level: X-Spam-Status: No, score=-1.248 required=5 tests=[BAYES_00=-2.599, HTML_10_20=1.351, HTML_MESSAGE=0.001, SPF_PASS=-0.001] Received: from postgresql.org ([200.46.204.71]) by localhost (mx2.hub.org [200.46.204.254]) (amavisd-new, port 10024) with ESMTP id AEjFyu68VetH for ; Tue, 18 Apr 2006 15:19:22 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nz-out-0102.google.com (nz-out-0102.google.com [64.233.162.201]) by postgresql.org (Postfix) with ESMTP id C88F711F6B12 for ; Tue, 18 Apr 2006 15:19:21 -0300 (ADT) Received: by nz-out-0102.google.com with SMTP id z3so781462nzf for ; Tue, 18 Apr 2006 11:19:20 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type; b=Cr3BGhoSJVlLQSxNPeFWlfDQUhJ309/CPbhp57QNJdOSjKaQwNuQvW4qErkPghnXPMS+UzapjKaqvK+0r1CQG8iOHvV75Ip2ex3+jekg70MbFOK/3OBb3UZF3H4XKBxrCRvNyiTIfdKUf7mQlvlQ2akXyhcRjXfQg2sIuevkMrg= Received: by 10.65.43.3 with SMTP id v3mr851708qbj; Tue, 18 Apr 2006 11:19:20 -0700 (PDT) Received: by 10.65.197.10 with HTTP; Tue, 18 Apr 2006 11:19:20 -0700 (PDT) Message-ID: <647d31c20604181119m43ba3c7ay3d467dd9446a6fd8@mail.gmail.com> Date: Tue, 18 Apr 2006 14:19:20 -0400 From: "Mark True" To: pgsql-sql@postgresql.org Subject: Looking for some help with cascading updates... MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_23185_30435664.1145384360263" X-Archive-Number: 200604/114 X-Sequence-Number: 24923 ------=_Part_23185_30435664.1145384360263 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Here is the question: I have a situation where I need to create triggers to cascade an insert operation to many tables to maintain foreign key constraints. So at a high level INSERT INTO myTable (Name, Address, Zip) VALUES ('Mark', '3 Dingle St.', '01832') myTable has a relation to two other tables, which contain Zip and Address. Before I insert into myTable I want to: Check if '3 Dingle St.' exists in table Address, if not insert it Check if '01832' exists in table Zip and if not insert it I want to generalize this so that for any insert into myTable, I guarantee that if Zip and Address are not populated they will be. My insert into myTable always contains full information required for doing the inserts int= o the other related tables. Help? --Mark ------=_Part_23185_30435664.1145384360263 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Here is the question:

I have a situation where I need to create = triggers to cascade an insert operation to many tables to maintain foreign = key constraints. 

So at a high level

INSERT INTO myTabl= e (Name, Address, Zip) VALUES ('Mark', '3 Dingle St.', '01832')

myTable has a relation to two other tables, which contain Zip and A= ddress.  Before I insert into  myTable I want to:

Check if= '3 Dingle St.' exists in table Address, if not insert it
Check if '0183= 2' exists in table Zip and if not insert it=20

I want to generalize this so that for any insert into myTable, I gu= arantee that if Zip and Address are not populated they will be.  My in= sert into myTable always contains full information required for doing the i= nserts into the other related tables.

Help?

--Mark

------=_Part_23185_30435664.1145384360263--