I am facing this issue with HSQL DB. Even if i try to create a simple view, i am unable to insert into it. Following are the steps I am trying:
1. create table test_emp(emp_id int, emp_name varchar, emp_age varchar, emp_sal varchar);
2. insert into test_emp values (4, 'rakesh', 29, 35000);//inserted some values into the table
3. CREATE VIEW emp_view as select emp_id,emp_name, emp_age, emp_sal from test_emp;// creating a view displays the values which are available in table.
4. INSERT INTO EMP_VIEW (emp_id,emp_name, emp_age, emp_sal) VALUES (9, 'naresh', '41', '42000');//this line gives error
[Error Code: -55, SQL State: S0005] Not a Table: EMP_VIEW in statement [INSERT INTO EMP_VIEW]
It seems it will accept only a table name here.
I am using Temp DB so all the tables, view and even the db gets deleted once session is completed.
You can't insert into a view (usually), a view is a read only way of looking at the database.
A view can select from many tables and have a complicated where clause with outer joins and inner joins, unions and subselects. How can the database know what to insert where, how would it handle mandatory columns that were on the table but not in the view?
probably I could use INSTEAD OF trigger, but it is also giving me error.
CREATE TRIGGER EMP_Trigger on EMP_VIEW
INSTEAD OF INSERT
--Build an INSERT statement ignoring inserted.ID and
INSERT INTO TEST_EMP
SELECT emp_id,emp_name, emp_age, emp_sal
ERROR: Unexpected Token INSTEAD, requires ON in statement