Update View which have Aggregate function

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 756
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

Comments or Responses

Login to post response