A view can be defined as a virtual table or a stored query and the data accessible through a view is not stored in the database as a distinct object. Only the select statement is stored on the database instead.
How ever views can be used and perform DML operations (Insert, Update & Delete) also.
Consider the following two tables.
Now create the following views.
You can insert data to the above tables using the views we have just created. And it is the same syntax that we use to insert data to tables.
And if you query the tables you can see that the records have inserted correctly.
Now we will create the following view. This time we will join two tables and create a somewhat complex query.
Using the above created view we can list the last payment details of each student.
So if we required to insert last payment details using this view how shall we do it ? If you use the simple insert statements similar to the ones, we used earlier, you have could ended up with the following error.
In order to insert (update & delete) data to views created using multiple tables, you need to use an ‘Instead of trigger’.
**Please note that ‘After Triggers’ cannot be created for views.
Let’s create an instead of trigger using the following syntax.
Now using the above insert syntax, you can insert data without getting any error. If you inspect the ‘STUDENT_PAYMENT’ table you can see that the data has been inserted successfully.