Re: Not sure how to approach this

jctown_at_nb.sympatico.ca wrote:
> I have 3 tables.
> 1) books
> 2) publishers
> 3) subjects
>
> I *think* I need to use a trigger but not sure.
> I've loaded up publishers with distinct publisher from books and
> subjects from distinct subject from books. Now I want to set
> *something* up so that if I add a new row to "books" a check will be
> made on publishers and subjects to see if something new is being added
> and then add a row if this is the case.
>
> Something like
>
> On_New_Book
> insert into publishers
> select publisher from book
> where book.publisher not in (select publisher from book)
> insert into subjects
> select subject from book
> where book.subject not in (select subject from book)
>
> I don't know how close or far away I am. I'm not even sure if a
> "trigger" is what I need here.

Constraints is what you are looking for. Unique constraints in this particular case. Set up unique constraints on publishers and subjects and the database engine won't let you insert duplicate rows into either. Foreign key constraints on books referencing publishers and on books referencing subjects are also due - they will not allow you to create records about books without publisher or subject. It's that easy.

Hth,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Sun Sep 03 2006 - 20:56:57 CEST