Update View which have Aggregate function

If the VIEW is having AGGREGATE fucntions, it will not allow to do update on the view. To do so INSTEAD OF Trigger is compulsory.

CREATE VIEW COUNT_OF_EMPs
AS
	SELECT DEPARTMENT_ID, COUNT (EMPLOYEE_ID) NO_EMPS
	FROM emp_dump
	GROUP BY DEPARTMENT_ID
GO
SELECT * FROM COUNT_OF_EMPs
GO
UPDATE COUNT_OF_EMPs SET DEPARTMENT_ID=200 WHERE DEPARTMENT_ID=10 
GO
--Error msg: Msg 4403, Level 16, State 1, Line 1 Cannot update the view or function 'COUNT_OF_EMPs' because it contains aggregates, or a DISTINCT or GROUP BY clause, or PIVOT or UNPIVOT operator.

--we can update that type of view by using INSTEAD OF trigger

CREATE TRIGGER tr_InsteadOf_UPDATE_VIEW
ON COUNT_OF_EMPs
INSTEAD OF UPDATE 
AS
BEGIN
	IF update(NO_EMPS) --the updation of no_emps can be logical error
		raiserror('The no. of department is not updatable', 16,1)
	else
		UPDATE emp_dump  SET DEPARTMENT_ID =i.DEPARTMENT_ID 
		from emp_dump e, inserted i, deleted d 
		WHERE e.DEPARTMENT_ID=d.DEPARTMENT_ID
END;
GO

UPDATE COUNT_OF_EMPs SET  NO_EMPS =45 where DEPARTMENT_ID = 80;
GO

SELECT * FROM COUNT_OF_EMPs
GO