Untitled

CREATE TABLE [Messages] (
	Id INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,
	Message nvarchar(500) not null
)
ALTER TABLE Orders DROP CONSTRAINT FK_Orders_Employees;
ALTER TABLE Orders Alter Column EmployeeId int null;
ALTER TABLE Orders ADD CONSTRAINT FK_Orders_Employees FOREIGN KEY (EmployeeId) REFERENCES Employees(Id) ON DELETE NO ACTION;

CREATE OR ALTER TRIGGER D_AFTER_Employees ON Employees
AFTER DELETE
AS
BEGIN
	INSERT [Messages]([Message])
	SELECT 'Сотрудник выполнил ' + CONVERT(varchar(10), COUNT(o.Id)) + ' заказов'
	FROM deleted d
	LEFT JOIN Orders o ON d.Id = o.EmployeeId
	GROUP BY o.EmployeeId;
	Update Orders set EmployeeId = null where EmployeeId in (select Id from deleted);
	Delete from Orders Where EmployeeId in (select Id from deleted);
END;