create table Table4
(
id integer not null identity(1, 1) constraint PK_Table4_1 primary key,
x integer constraint FK_Table4_2 references Table4(id) on delete no action on update no action
);

id | x
2 | null
3 | 2
4 | 2
5 | 2

create trigger casc_table4_1 on Table4 instead of delete as
begin
delete from Table4 WHERE x in (select id from deleted)
end;

delete from Table4 where id = 2;

__>>delete from Table4 where id = 2;
__>>

Сообщение 547, уровень 16, состояние 0, строка 1
Конфликт инструкции DELETE с ограничением SAME TABLE REFERENCE "FK_TABLE4_2". Конфликт произошел в базе данных "TEST1", таблица "dbo.Table4", column 'x'.
Выполнение данной инструкции было прервано.

__>delete from Table4 where id = 2;
__>

begin
    delete from Table4 WHERE x in (select id from deleted)
    delete from Table4 WHERE id in (select id from deleted)
end;

create trigger casc_table4_1 on Table4 instead of delete as
begin
delete from Table4 WHERE x in (select id from deleted)

delete from Table4 WHERE id in (select id from deleted)
end;