When I setup a relationship in Access I can specify that Primary Key deletes cascade down to the Forgien Key. So when I delete an Order Header it cleans up all the items in the Order Details table for me automatically.
Can I get this same functionality in SQL Server 7 without having to write triggers or are triggers the only way?
No, trigger is not the only way: you could create a procedure that does this for you or, when you create the table or add the constraint specify the on delete option to cascade (see BOL, create table).
Seriously...be careful with cascading...should be no need...
pretty dogmatic Mr. Kaiser, what's your solution to my previous example, if in fact I don't care about history? Say I have OrderNumber as the Primary key in the OrderHeader table and OrderNumber as a Foreign key in the OrderDetails table, how is this a misconfigured key arrangement?
Seems the SQL Server developers were too lazy to say "Enable relationship for INSERT, UPDATE and DELETE. My bad, I read the help screen and found that DELETE is included with this option, however, it doesn't give me the desired result. It actually disables Primary key deletion if Forgein key dependants exist.
Again, do I need to write triggers to accomplish my goal here
I want all Forgein keys associated with a Primary key to be deleted when I delete the PK record