In the design of the foreign key you can specify a delete rule. Like you said the CASCADE option is what you need. The following is from BOL:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/08812343-e9a6-4a0f-91fd-80d95ab4b71f.htm

INSERT and UPDATE Specification Category

Expand to show information for the Delete Rule and the Update Rule for the relationship.

Delete Rule

Specify what happens if a user tries to delete a row with data that is involved in a foreign key relationship:

No Action An error message tells the user that the deletion is not allowed and the DELETE is rolled back.

Cascade Deletes all rows containing data involved in the foreign key relationship.

Set Null Sets the value to null if all foreign key columns for the table can accept null values. Applies to SQL Server 2005 only.

Set Default Sets the value to the default value defined for the column if all foreign key columns for the table have defaults defined for them. Applies to SQL Server 2005 only.