CREATE TABLE [dbo].[Organization]
(
	[Id] BIGINT NOT NULL PRIMARY KEY IDENTITY, 
    [Name] NVARCHAR(1000) NOT NULL, 
    [Description] NVARCHAR(MAX) NULL
)

CREATE TABLE [dbo].[Group]
(
	[Id] BIGINT NOT NULL PRIMARY KEY IDENTITY, 
    [OrganizationId] BIGINT NOT NULL, 
    [Name] NVARCHAR(1000) NULL, 
    [Description] NVARCHAR(MAX) NULL, 
    [Icon] INT NULL, 
    [Type] INT NOT NULL DEFAULT 1, 
    CONSTRAINT [FK_Group_Organization] FOREIGN KEY ([OrganizationId]) REFERENCES [Organization]([Id])
)

CREATE TRIGGER [OrganizationDelete]
ON Organization
FOR DELETE
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @Id bigint; 
	SET @Id = (SELECT Id FROM deleted);
	DELETE FROM [Group] WHERE [Group].OrganizationId = @Id;
END

	SET NOCOUNT ON
	DECLARE @Id bigint; 
	SET @Id = (SELECT Id FROM deleted);
	DELETE FROM [Group] WHERE [Group].OrganizationId = @Id;

FOR | AFTER  
Тип AFTER указывает, что триггер DML срабатывает только после успешного выполнения всех операций в инструкции SQL,
 запускаемой триггером.  
Все каскадные действия и проверки ограничений, на которые имеется ссылка, 
должны быть успешно завершены, прежде чем триггер сработает. 

Если единственным заданным ключевым словом является FOR, 
аргумент AFTER используется по умолчанию. 

CREATE TRIGGER [OrganizationDelete]
ON Organization
INSTEAD OF DELETE
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @Id bigint; 
	SET @Id = (SELECT Id FROM deleted);
	DELETE FROM [Group] WHERE [Group].OrganizationId = @Id;
	DELETE FROM [Organization] WHERE [Organization].Id = @Id;
END

SET @Id = (SELECT Id FROM deleted);

delete g from Group g inner join deleted d on d.id = g.OrganizationId