Thank you for the article.

I am trying to find the best way to maintain some info across a number of child tables by a value in a parent table. 

Table structures are as such:

(parent)

Author_Info
|id|name|active|
|1|Poe|1|
|2|Roth||1|
|3|Twain|0|

(child)

Books
|id|author_id|title|active|
|1|1|Tell tale heart|1|
|2|1|The Pit and the Pendulum|1|
|3|2|Cabin Fever|1|
|4|2|Hostel|1|
|5|3|Tom Swayer|0|
|6|3|Huck Finn|0|

Currently, I have a trigger that when a author's active column is changed, it updates the 'active' column in the all the records in the 'Books' table  that match that authro's id.

I am chaning this to use a multi-column FK on (author_id, active) against (id, active) of the Author_Info table that ON DELETE No Action, ON UPDATE Casecade. Is this a better solution?

Thanks.

Rather than store an active flag in the child tables, just store it in the parent table and let the parent dictate if the titles are active.  That way, you don't need a trigger and you don't have any db overhead in maintaining the child table entries.  The way you described it, it sounds like either the titles are all active or all inactive depending on the status of the parent (author).

 Thanks for the reply.

Yeah, that was what I was thinking about after writting this question out. I thought there was a reason our application needed it to be done that way, but we'll see. I have been testing out some things doing it this way since i posted this yesterday.

Thanks again.

Thanks for the article.  I have one question though:

At the moment, I have a relationship between two tables product and customer and they're linked by an order_id column in each table and using a foreign key relationship.  However, when inputting data into the product table I want it to make sure that the order_id in the customer table already exists, meaning that you won't be able to enter product until the parent order_id in the customer is created.

To clear it up some...
Customer table has primary key "order_id".
Product table has foreign key "order_id".
I want to make sure that when you enter the foreign key, there must be a matching primary key in customer.

Is this possible?  If so, would this be done using a trigger?  If so, how would I go about doing that?  As I'm clueless with pl/sql at the moment.  (In 10th grade, we dont start pl/sql until next semester D=)

Hi... it's nice to see such a young person interested in databases.  I think you'll find it to be a rewarding endeavor.

 In your case, I think what you're looking for is 3 tables

1. a Customer table
2. a Product table
3. an Orders table (and probably a Line_Item table)

 You would have a PK on each table (i.e. Customer_id, Product_id, and Order_id)

 The Orders table would contain the Customer_id and Product_id in addition to Order_id  (along with any other columns you'd need).   You would add a FK for the Customer_id and Product_id which would, in effect, say that "You can't have an order unless (a) you have a valid customer and a (b) a valid product".

 I recommend you download the AdventureWorks sample database from CodePlex where you can examine a sample Orders schema that contains sample data that you can experiment with.

Let me know if you're not clear and I can try to work it through a bit more with you.

Aha, that was exactly how I planned it at the beginning, however it didn't work out because an order can have multiple products attached to it.  Thus, I'd need a bunch of optional foreign key product fields? Lol...(And I only got rid of my Orders table because my programming teacher's motto is "Simple, simple simple" hence she told me to get rid of what she thought was redundant data in the Orders table >_<)  Lol.

But now that I think about it....Instead I can bring back the Orders table, and have a reference to the Customer (like you said).  But instead of referencing the Products in the Orders, I'll reference the Orders in the Products.  This way, we'll have a Customer and his info.  Then the Order will reference the Customer_ID and have w/e info.  And each Product will have a reference to that Order_ID.  Oh snap!  Might that work?

Noooo...Because this way I'll still be able to input a value into product.order_id for an Order that hasn't been created yet.....My goal is to have some type of constraint that will stop me from inserting a foreign key value that has no matching primary key already in the database.  Basically, if the primary key is not in the database, you can't enter this foreign key.  So, I went full circle.  Is this possible with triggers?

Lol, thanks for the help and the recommendation towards AdventureWorks, although I've got almost 400records plus some old stuff from Oracle I can use...and I apologize for being so confusing.  ^_^  Perhaps I'm missing something, and the way you explained it was correct....

 <<Aha, that was exactly how I planned it at the beginning, however it didn't work out because an order can have multiple products attached to it.>>

That's where a LineItem table comes into play.  In the classic sense, a line item represents an ordered product in a customer's order

Think of it this way.   Think of the nouns that you need for your model by forming
a statement about what you're trying to solve:

A customer can order one or many products 

You would need:

A customer
A product
An order
A line item (to represent more than 1 product per order)

Now think about how they relate to each other.  This would determine your FK constraints.

Can a customer exist without a product?  Yes, so there's no relation

But...

Can an order exist without a customer?  No.  So customer_id would be a FK in the Orders table.

When you get into transactions, you'd need to be aware that triggers can extend the life of the transaction which could cause a condition called blocking where readers can't get data they need becuase someone is writing data and vice versa.  They have their place but they're not my choice for data checking.