I have created views for employees in different departments. Each employee is able to view records for his own department in the view. Also employees can update or insert records for their department through that view. I noticed that employees are even update and insert data that is related to any other department.
Now it was required to restrict that no employee can insert/update data that conflicts with department restriction that i have implemented in each view. For example consider the following imaginary view where records are picked based on a department provided in where clause
Create VIEW UVW_DeptRecords AS SELECT name, designation, department FROM employees WHERE department = 'Finance' GO
Employees from finance department are able to perform DML operations on this view. It is required that these employees may not be able to insert a record with department name other than Finance. First i planned to use a trigger for implementation of this logic. But while discussing this issue with fellow DBA i came to know that there is an option provided with the view definition itself, that will prohibit any DML that conflicts with the conditions provided in where clause of view definition.
So creating the above view with additional WITH CHECK OPTION in following way will not allow any DML operation for which employee does not belong to Finance department.
Create VIEW UVW_DeptRecords AS SELECT name, designation, department FROM employees WHERE department = 'Finance' WITH CHECK OPTION GO