DML Operations on Views

Views are used for many purposes including data hiding and giving limited access to end users or NON-DBA schema in Database. When we make visible the less secure column publicly using views at the same time we need to grant some DML on those views without involving base tables.

Here we will focus on these areas.

1. Complex views.
2. Simple views. 
3. Key preserve keys.

DML on complex views.

Complex views can involve more than two tables, complex calculations, inline sub queries, functions called in column selection list, aggregates or grouping data sets. These kind of views are absolutely not allowed for performing DML operations. If it’s really necessary then we need to think about INSTEAD OF Triggers despite of their maintenance issues.

DML on Simple Views. 

A simple view based on single table having less calculated expressions. These kind of views are very easy to be manipulated in term of DML operations. In a simple view these following elements can be involved.

1.       Distinct Keyword.

2.       Aggregate function.

3.       Group by clause.

4.       Expression ( Any Expression or calculated derived column Salary * 12 as ANNUAL_INCOME)

5.       ROWNUM pseudo Column.

6.       ROWID pseudo Column.

7.       Not null column constrained column not listed in selection column list of View.

Here I will discuss some cases when operation is allowed on simple view or not. First we will take a look on this table to get the abstract of all the rules.

	Distinct	Aggregate	Group by	Any Expression	ROWNUM	ROWID	Missing Not null column In View	Key Preserve Tables
Delete	True	False	False	False	False	True	True	True
Update	True	False	False	False/True	False	True	True	True
Insert	True	False	False	False	False	False	False	True

You can use this SAMPLE codes.

If we analyze the table, all the cases are very straight forward. For example a simple view having DISTINCT keyword could be allowed for DML operations similarly view having ROWID will allow for delete and update but not for Insert. On the other hand we have a special case which is view having Expression e.g

Create Or Replace View With_Expression As

Select  Employee_Id , Department_Id , First_Name ||  Last_Name As Full_Name, Salary * 12 Anual_Salary

From   Employees

Where  1 = 1 ;

Delete and insert is clearly not allowed. But Update is TRUE/FALSE. This true and false is depend on WHERE clause that we use in update. If we use expression in WHERE clause and simple column in SET clause to update in this case it’s TRUE and if we use simple columns in WHERE clause to update expression in the SET clause in this case it’s FALSE.

Update With_Expression

Set    Full_Name = 'XYZ'

Where  Department_Id = 90  ;

Result: False

Update With_Expression

Set    Anual_Salary = 90

Where  Department_Id = 90  ;

Result: False

Update With_Expression

Set    Department_Id = 100

where  Anual_Salary = 108000 ;

Result: True

DML on Key preserved

let's say we have two tables. One is master table which is Department. One is child which is Employees. When we join these two tables the DEPARTMENT_ID will be repeated against each corresponding child entry in employees tables but the EMPLOYEE_ID in this join will not repeated. So in this case our Key preserve table is Employees where each entry Appear in the result at most one time. 

for more Ask Tom

According to our table, all the operations are allowed satisfying other conditions. But only satisfying rows should be from the table which having now repetition of PK in the result set of View. 

Here are some experiments.

Create Or Replace View Dpt_Emp_V As 

Select         

       Departments.Department_Id  Dpt_Id,

       Departments.Department_Name,

       Employees.Employee_Id      ,

       Employees.First_Name       ,

       Employees.Last_Name        ,

       Employees.Hire_Date        ,

       Employees.Salary

From   Departments

Inner Join Employees

On     Departments.Department_Id = Employees.Department_Id ;

Note: We are not including DEPARTMENT_ID from Employees tables.

Update On DPT_EMP_V

Update Dpt_Emp_V 

Set First_Name = 'MODIFIED' 

Where  Dpt_Id = 90 ;

Result: True

Because "First Name" belongs to Key preserved table and the column in WHERE clause is belong to Non-Key Preserved. 

Now let's reverse the clock. 

Update Dpt_Emp_V 

Set Dpt_Id = 500 

Where  employee_id = 101 ; 

Result: False  

Because DPT_ID belongs to Non-Key Preserve table. 

Delete On DPT_EMP_V

Here is very tricky point:

if we issue a delete statement on this view having filter on Non-Key preserve table's column the rows will be deleted from Key-Preserve tables. 

let's give it a try. 

Select count(*) From Departments Where 1 = 1 ;

count: 27

Select * From EMPLOYEES Where 1 = 1 ;

count: 107

DELETE FROM Dpt_Emp_V WHERE Dpt_Id = 90;

3 Rows Deleted.

Select * From EMPLOYEES Where 1 = 1 ;

Count: 104

Rollback; 

Explanation:

We didn't include DEPARTMENT_ID from employees table but when we issue DELETE command after satisfying all the conditions rows are deleted from Key-Preserve table. 

Insert On DPT_EMP_V

Satisfying all the insert criteria the values can be inserted on this view but the insertion will be occurred in Key-Preserve table which is, in our case is EMPLOYEES.

Conclusion: 

It's always been considered that simple views or views having two tables can easily be modified using DML statements but it's not true in all the situations. INSTEAD of triggers can be a good idea to handle these situations but still we should seriously need to figure it out do we really need triggers to updated hidden column's data because of maintainability of triggers. Maintaining manipulations on views depends on business logic.