1

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?

Kevin Amorim
  • 527
  • 5
  • 18
  • 2
    You could also just use [cascade delete](http://stackoverflow.com/questions/6260688/how-do-i-use-cascade-delete-with-sql-server). There is no best practice. It depends on various factors – Liam Apr 22 '15 at 08:59

1 Answers1

0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
teo van kot
  • 12,350
  • 10
  • 38
  • 70
  • Thanks, I tried to apply the Cascade rule to the Delete action and the problem is: When I delete a row in A, the row in A_B get's deleted but not the row in B. Remind that A has no foreign key to B, and B has no foreign key to A, everything is mapped by the table A_B. – Kevin Amorim Apr 22 '15 at 12:58
  • I read again your answer and when trying to implement it on the database I started to notice that I may be overthinking this and maybe this is more of a design problem. The cascading rule should be enough for this. Thanks for the help, and also for the tip on coding the logic for this in the DAL. – Kevin Amorim Apr 22 '15 at 13:10
  • Can you just please check the edit I made to the main question. I think the situation is more clear now. – Kevin Amorim Apr 22 '15 at 14:02
  • Don't use triggers - that's what i suggest. Trigges is not easy to maintain in future. Just put that logic in the stored procedure where you deleting your record. – teo van kot Apr 22 '15 at 14:10
  • Hum, ok. I though that there could be a simple constraint or something that would add another level of 'security'. I will then put the code to delete all related row in the DAL. Thanks for your time. – Kevin Amorim Apr 22 '15 at 14:17