December 9, 2003 at 4:43 am
Hi folks,
I am wondering what is the best way to cascade changes to related tables.
eg, I have 2 tables that reference a main table, by ID.
I realise that by right-clicking the relationship, and by checking:
1) Enforce relationships for INSERTS and UPDATES
2)Cascade updated related fields
3)Cascade deleted related fields
..this will cascade all the actions on the referenced table down to the related tables.
Is this the way the the more seasoned of you go, or do you use triggers etc?I'm looking to get this right from the start. 🙂
cheers,
yogi.
December 9, 2003 at 6:45 am
This is a tricky question because the answer is very dependent on the application.
When you have large hierarchies and it is a controlled environment (security is well defined) the Cascade delete prove to be VERY fast, easy and useful for me. I usually don't use the cascade updates because if the keys are surrogates then they won't be updated much. So as long as it is CONTROLLED cascades deletes is ok
HTH
* Noel
December 9, 2003 at 12:34 pm
Hi Noel,
I see your point about the cascade updates being largely superfluous..good one.
When you mention the fact that the application has to be controlled, do you mean that eg the user should only have access to the views/underlying base tables via well defined sprocs..something along this line?
cheers,
yogi.
December 9, 2003 at 1:08 pm
The Concern here is that cascade delete is a VERY destructive action.
Suppose you have a hierarchy of 3 tables and all are "cascade enabled on delete" and you delete a record on the parent table,
ALL child records on the immediate table are going to be deleted and all childs of each child in the 2 are going to be removed from the 3rd,
so the person who has permission to delete from the parent should REALLY KNOW WHAT IS DOING!!!
* Noel
December 9, 2003 at 3:54 pm
Hi Noel.
I understand now.
Thanks bud.
yogi
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply