I am in the design stage of an app and have developed a series of "rules" regarding the inserting/updating/deleting of data that I could use some advice on. I originally thought to store these rules in DBC stored procedures, which works for simple checks like don't allow deletes if status <> OPEN.

The problem is that some of my rules are more complex, like "Every time a record is inserted into table X, create a record in table Y". This usually will involve taking a value from the new record in X, doing a lookup in a support table and then inserting a record in Y with the data returned from the support table.

SOOO, my question is whether a stored procedure is the right place to do this, or should I handle this in the objects that manage the data ? I had thought stored procedures would make it easier and enforce consistancy, but believe I may be creating a monster. Any comments/thoughts would be appreciated

Sorry for the long post
Rob Hughes