SQL & PL/SQL

My Oracle environment

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
"CORE	10.2.0.2.0	Production"
TNS for Linux: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

I need to constrain a table to only allow child records to be related to a parent record if the relationship is correct as identified in another table that contains all of the "legal" relationships.

The table that needs this constraint is something like:

CREATE TABLE activities
(
    activity_key        NUMBER NOT NULL, -- primary key in this table
    activity_type_key   NUMBER NOT NULL, -- foreign key to a primary key in a code table
    parent_activity_key NUMBER           -- foreign key to another record in this table
}

The constraint needs to ensure that the Activity Type of the parent activity is allowable with respect to the Activity Type of the child activity, as defined by another table.

The other table design is something like:

CREATE TABLE act_type_act_type_assocs
(
    activity_type_key_parent  NUMBER NOT NULL, -- foreign key to a primary key in a code table
    activity_type_key_child   NUMBER NOT NULL  -- foreign key to a primary key in a code table
}

What needs to happen is when a record is inserted or updated into the ACTIVITIES table, and if the PARENT_ACTIVITY_KEY is NOT NULL, then the Activity Type of the parent activity and the child activity need to be checked against the ACT_TYPE_ACT_TYPE_ASSOCS table to see if the record exists. If it does not, then don't allow the insert or update.

I can easily do this using a trigger (I believe), but I know that some of the professionals in this forum feel strongly that triggers should be avoided, if possible. I am always open to better approaches, but cannot figure out how to do this as a function based constraint. I don't have much experience with function based constraints, but from what I've read, the most common examples are easy to understand and implement. But I haven't found anything that discusses querying a separate table.

One thought I had was to write a procedure to validate the relationship, but I'm unsure of how to write a function based constraint to utilize this procedure.

I have complete flexibility in coming up with a solution, however I would like to not change any aspect of the current ACTIVITIES table design (such as changing existing columns) since that would also require a change to the application that uses this table. In short, I want a database solution that will at most uncover issues in the application that are allowing "illegal" activity type to activity type relationships.