I have the following table:
if object_id(N'dbo.Node') is null
create table dbo.Node
(
ID bigint identity primary key,
ParentID bigint null foreign key references Node(ID) on delete no action,
DateCreated datetime not null,
LastUpdated datetime not null,
[Name] nvarchar(500) not null,
);
Now, because SQL Server complains when I try to set the foreign key as a cascaded delete, I have created a trigger to do the work:
create trigger Node_Delete on Node for delete
as
begin
delete from Node where ParentID in (select id from deleted)
end
Now here's a sample data set:
ID ParentID DateCreated LastUpdated Name
520 1 2010-01-12 02:26:26.890 2010-01-12 02:26:26.890 Test 1
523 520 2010-01-12 02:32:44.777 2010-01-12 02:32:44.777 Test 2
Now let's run this bit of SQL:
delete from Node where ID=520
The node should delete, along with the child node. So why do I get this error?
Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the SAME TABLE REFERENCE constraint "FK__Node__ParentID__117F9D94". The conflict occurred in database "mydb", table "dbo.Node", column 'ParentID'.
The statement has been terminated.