Re: Insert data into multiple tables

On Tue, 24 Apr 2001, will trillich wrote:

> On Tue, Apr 17, 2001 at 05:37:31PM -0500, K Old wrote:

>     create rule cust_insert as
>         on insert to newcust
>         do instead
>         (
>             insert into customers
>                 NEW.client_id,
>                 NEW.addr_id,
>                 NEW.account_type,
>                 NEW.how_hear,
>                 NEW.cc_type,
>                 NEW.cc_exp,
>                 NEW.cc_num,
>                 NEW.sign_up_date
>             ;
>             insert into addresses
>                 NEW.addr_id
>                 NEW.fname,
>                 NEW.lname,
>                 NEW.company,
>                 NEW.addr1,
>                 NEW.addr2,
>                 NEW.city,
>                 NEW.state,
>                 NEW.zip,
>                 NEW.country,
>                 NEW.phone,
>                 NEW.fax,
>                 NEW.email
>             ;
>         );
>
> BUT -- if this is a 1:1 relation, and customers ALWAYS have
> addresses, and addresses ALWAYS have customers, then just make
> the sucker into one table and you're done.

Yep. RULEs kick ass.

If this is a 1:1 relationship, and you don't mind wandering slightly
afield of the SQL standard, inheritance is a good way to go, too.

--
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington