Cascade deletes on linked table joining 2 tables that each have a cascade delete to another table. How?

Question

Let's assume the following objects as tables in SQL:

• Companies
• Employees (refers to companies)
• Meetings (also refers to companies)

And employees can be in meetings so we have a link table:

• EmployeeMeetings (i.e. FK to both employees and meetings)

Furthermore I can't touch the Companies table (e.g. no triggers on this). What I'd like to have is that:

1. All employees are removed if a company is removed
2. All meetings are removed if a company is removed
3. All EmployeeMeeting records are removed if either the employee or meeting is deleted

Unfortunately I'm stuck as this provokes the dreaded "may cause cycles or multiple cascade paths". How does one work around this given the constraints? I guess I can't even put both FKs in or there's a risk that after a delete of a company, either an employee or meeting can't be deleted as the FK in EmployeeMeetings will prevent this. Right?

Answer 1

If I were you, I'd avoid triggers and cascading deletes altogether. They always end up working in unexpected ways.

Stored procedures are easy to understand compared to triggers and cascading deletes. So I'd create a stored procedure that removes meetings and employees before the company:

create procedure dbo.RemoveCompany(@companyId int)
as
delete * from employees where CompanyId = @companyId
delete * from meetings where CompanyId = @companyId
delete * from companies where Id = @companyId

As an added bonus, stored procedures create an explicit contract between your database and the application.

Answer 2

Have cascade delete from Companies to Employees, from Companies to Meetings and from Employees to EmployeeMeetings. Add a trigger after delete on table Meetings that deletes in EmployeeMeetings.

create trigger Meetings_Delete on Meetings after delete
as
begin
  set nocount on;
  delete EmployeeMeetings
  from deleted
  where deleted.MeetingsID = EmployeeMeetings.MeetingsID
end