ASP MVC Delete only the relation between 2 tables (many-to-many relation)

Question

Using ASP MVC with active record.

Ive got 2 tables with records that sometimes are related and sometimes aren't. The relation is defined by the user. 1 table has projects, the other has devices. Projects can be created and deleted, devices cannot. When a user deletes a project, all relations between that project and the devices should be removed, but the devices should remain.

How do I do this?

my delete action currently looks like this:

public ActionResult Delete(int id, FormCollection collection)
  {
     if (!Project.Exists(id)) return RedirectToAction("Index/1", "Error");

     try
     {
        Project project = Project.Find(id);


        if (project.User.Id != SessionVariables.AuthenticatedUser.Id) return RedirectToAction("Index/1", "Error");

        project.DeleteAndFlush();

        return RedirectToAction("Index", "Project");
     }
     catch(Exception e)
     {
        return RedirectToAction("Index", "Error");
     }
  }

Answer 1

So if I understand you correctly, when a project is deleted from the projects table you want to delete all entries in your projectdevice mapping table? (I assume you are using a many to many relationship from another one of your questions).

If that's the case, I would recommend you use a database trigger, rather than try and manually do this in code. You can create a trigger which removes all rows for a specific project in the mapping table, when a project is deleted from the project table. If you're using MS SQL something like:

CREATE TRIGGER trig_deleteProjectDevice
ON projectTable
FOR DELETE
AS
DECLARE @projectID INT
SELECT @projectID = (SELECT projectId FROM Deleted)
DELETE FROM projectDeviceMappingTable WHERE projectId = @projectID