Normally I would say set up your foreign keys with a cascade delete e.g.
CASCADE in SQL Server with example[
^] but beware which table you make the "parent" and which tables are "child" tables - I doubt you want to delete an Employee just because the Role disappears - you might want to reallocate them!
I thought about this again, and if your Role table is not the "parent" in the above context, but you want to change the other tables if a role is deleted, then you can create a Trigger on the Role table to be fired
AFTER DELETE - an example can be found here
http://www.tech-recipes.com/rx/41468/sql-server-coding-the-after-delete-trigger-in-sql-server/[
^] and on CodeProject here -
Triggers -- SQL Server[
^]
Submit an article or tip