SQL help needed: Joined tables not allowing insert on view

[blandishments]

Hi

I need some help with sql.

I don't know how to do this or whether it's possible.

I'm joining two tables in a view and I want to insert values through the view.

Below is a snippet example that shows what I'm looking to do.

drop table testtable1
drop table testtable2
drop view testview
CREATE TABLE testtable1 (id INTEGER NOT NULL, txt VARCHAR (512), CONSTRAINT testtable1 PRIMARY KEY (id))

CREATE TABLE testtable2 (id INTEGER NOT NULL, txt VARCHAR (512), CONSTRAINT testtable2 PRIMARY KEY (id))

create view testview AS select testtable1.* from testtable1, testtable2 where testtable1.id=testtable2.id

insert into testview (id,txt) values (1,'test')

When I run this, I get an error saying:

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0150N The target fullselect, view, typed table, materialized query table,
or staging table in the INSERT, DELETE, UPDATE, or MERGE statement is a target for which the requested operation is not permitted. SQLSTATE=42807


Can someone help me with this? Thanks in advance.

 

[blom0344]

There are strict rules concerning updatable/insertable views. Joins do not fall within the scope AFAIK.
You should check INSTEAD OF TRIGGERs construction...

Ties Blom