Search code examples
sql-serverasp.net-mvctriggersrelationships

Best practice for deleting many-to-many relationships with SQL Server and ASP.NET MVC

I'm working on a project in ASP.NET MVC using Entity Framework linked to a SQL Server database, to which I use SQL Server Management Studio to manage.

I have some many-to-many relationships that I handle with an intermediate table that links the two other tables. Let's say I have table A, table B and table A_B that links the A and B in a many to many relationship.

If I want to delete a row in table A, I must delete all rows in table B that links with it, therefore deleting the rows in A_B.

My question is:

What's the best practice in this case? Should I handle those deletes in the controller in MVC ASP.NET or must I handle this with a SQL Server trigger?

To me it would look cleaner to just use a trigger and abstracting all this logic from the controller itself, so I would only need to delete the row in table A and the lower lever (SQL Server) would take care of the rest.

But, I've heard people saying that we should avoid triggers in SQL Server, so in a commercial project, which approach would you recommend as being the best practice?

Thanks.

EDIT:

[TABLE A [id]] -> [ TABLE A_B [id_A, id_B, position] ] <- [TABLE B [id]]

That's the situation I have (I can't post images yet). I've tried to put Cascade on Delete on every relationship, but when I delete a row in A, the corresponding row in A_B get's deleted but not the corresponding table in B. Is there a way to do this? Or in this case I have to use triggers?

Solution

  • If you really want to force SQL Server to delete references you should use CASCADE DELETE not triggers, just because it's more obvious way to do it. You should switch on it on FOREIGN KEY creation.

    If you already created the FOREIGN KEY then drop it first.

    ALTER TABLE dbo.B
ADD CONSTRAINT FK_A_B_Cascade
FOREIGN KEY (AId) REFERENCES dbo.A(Id) ON DELETE CASCADE

    But it's much better set your deletion logic in code. Because not in all cases you want to delete references. Sometimes it's better just set some flag like IsDeleted to true.

    Moreover it's better not doing this in Controller, but create separate DAL (Data Access Layer) project and place it there. And can be that you want to use one of ORM like Entity Framework to access your DB.