A table that has relation to itself issue

Question

I've defined table with this schema :

CREATE TABLE [dbo].[Codings]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ParentId] [int] NULL,
    [CodeId] [int] NOT NULL,
    [Title] [nvarchar](50) COLLATE Arabic_CI_AI NOT NULL,

    CONSTRAINT [PK_Codings] 
       PRIMARY KEY CLUSTERED ([Id] ASC) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

And fill it up with data like this :

Id          ParentId    CodeId      Title
----------- ----------- ----------- ----------
1           NULL        0           Gender
2           1           1           Male
3           1           2           Female
4           NULL        0           Educational Level
5           4           1           BS
6           4           2           MS
7           4           3           PHD

Now I'm looking for a solution, in order, when I delete a record that is parent (like Id = 1 or 4), it deletes all children automatically (all records that have a ParentId = 1 or 4).

I supposed I can do it via relation between Id and Parent Id (and set cascade for delete rule), but when I do that in MMS, the Delete Rule or Update Rule in Properties is disabled.

My question is: what can I do to accomplish this?

Thank you

Edit:

When I wrote (like Id = 1 or 4) I meant the records that are parent, not a child, and I don't mean query like like Id = 1 or 4

Some friend implied I can do it via a delete trigger, but I supposed I can do it via relation

Answer 1

You can accomplish it with a "DELETE TRIGGER" - just use it to delete any rows that have matching parents. It's essentially the same thing a cascade-delete would do.

CREATE TRIGGER t_Codings_delete
            ON Codings
         AFTER DELETE
AS
BEGIN

    DELETE Codings
      FROM Codings c
      JOIN DELETED d -- Virtual table containing rows you just deleted
        ON c.ParentId = d.Id

END