0

Let's say I have a database with table A and table B. B has a foreign key to table A, which does not allow nulls. When I try to delete and entity of A I want all references in table B to be removed as well. I try to do this with the following code:

using (var ctx = new MyDatabaseContext(ConnectionString))
{
    var a= new A() { IdA= idA};
    ctx.A.Attach(a);
    ctx.A.Remove(a);
    ctx.SaveChanges();
}

This results in the following error message:

Additional information: The DELETE statement conflicted with the REFERENCE constraint "FK_B_A". The conflict occurred in database "MyDatabase", table "dbo.B", column 'IdA'.

The statement has been terminated.

I have tried a lot, from using Triggers in the database to defining the ON DELETE CASCADE, but Entity Framework does fail. What am I doing wrong?

Trigger:

ALTER TRIGGER [dbo].[trg_DelA]
ON [dbo].[A]
FOR DELETE AS
  BEGIN
    DELETE FROM B WHERE B.IdA = IdA;
  END

BTW: This is just an example. The actual database is larger and also contains intermediate tables for many to many relationships.

BR Thomas

Community
  • 1
  • 1
Thomas Hahn
  • 332
  • 4
  • 13
  • Can you provide reproducible example? Both cascade delete and instead of delete trigger on table A should work and it doesn't matter - will delete be called by EF or manually. What exactly have you tried to do on DB side? – Andrey Korneyev Jul 13 '17 at 08:57
  • I added the trigger code to my post – Thomas Hahn Jul 13 '17 at 12:16

2 Answers2

2

AFTER (or FOR - they are synonyms) trigger are fired after triggering SQL statement. In your case this is too late, since deleting statement can't be completed due to foreign keys.

If you want to use trigger to handle cascade deletion - you have to use instead of trigger, and in this trigger first delete records from B table and then from A table.

So this could look like:

CREATE TRIGGER [dbo].[trg_DelA]
ON [dbo].[A]
INSTEAD OF DELETE AS
BEGIN
    DELETE FROM B WHERE B.IdA in (select IdA from deleted)
    DELETE FROM A WHERE IdA in (select IdA from deleted)
END

See MSDN for reference.

Andrey Korneyev
  • 26,353
  • 15
  • 70
  • 71
  • Oh I see. So there is no BEFORE but only an INSTEAD trigger? When I try to add this trigger I get the following message: Cannot alter INSTEAD OF DELETE or INSTEAD OF UPDATE TRIGGER 'trg_DelHarness' on table 'dbo.Harnesses'. This is because the table has a FOREIGN KEY with cascading DELETE or UPDATE – Thomas Hahn Jul 13 '17 at 13:03
  • I will try to remove the cascading from the foreign key – Thomas Hahn Jul 13 '17 at 13:06
  • Now I could create the trigger – Thomas Hahn Jul 13 '17 at 13:07
  • Thank you so much! It works. Now I have to apply these triggers to all my tables. – Thomas Hahn Jul 13 '17 at 13:08
1

Cascade your deletes.

Take a look at this: Entity framework code first delete with cascade

And this: https://msdn.microsoft.com/en-us/library/hh295843(v=vs.103).aspx

Kemal Güler
  • 608
  • 1
  • 6
  • 21