Skip to main content
Solved

Editable view

P
Rookie
Forum|alt.badge.img

Hi,

I am getting the following error message when I try to update a record in a view:

“View or function 'vw_bestel_leverancier_artikel' is not updatable because the modification affects multiple base tables.”

The view is created with a template with a from clause on the table inkoop_advies, with 2 joins to the artikel and leverancier table. Within this view, i want to update a record in a column that refers to inkoop_advies.aantal, which throws the error above. 

The table inkoop_advies has only a self reference, which might cause this issue.

I want to be able to edit in grid and change records in the view that refer to inkoop_advies.aantal. I can create a default that updates the correct row, but that will still trigger this error, but change the value nevertheless.

Could an instead of update trigger be the solution? I am not sure how to implement that.

Or can this be solved differently?

 

Thanks in advance.

 

 

 

 

Best answer by Mark Jongeling

Hi Philip,

Indeed, to make the view update successfully you'll need to create an Instead of trigger. 

In the Software Factory, go to Functionality and create a new Control procedure with code group 'INSTEAD_OF_TRIGGERS’. In template, the mutated data will be available through two tables namely inserted and deleted.

Some examples:

Insert:

Show content
insert into table (name)
select i.name
from inserted i

 

Update:

Show content
update t
set name = i.name
from table t
join inserted i
  on i.id = t.id
join deleted d
  on d.id = t.id
where d.name <> 'Admin' --Can't change the name for Admin

 

Delete:

Show content
delete t
from table t
join deleted d
  on d.name = t.name

 

Thereafter assign it to the view on the correct program object (ii for insert, iu for update, id for delete) and you can execute it at Result on the database.

Hope this helps!

View original
Did this topic help you find an answer to your question?
This topic has been closed for replies.

3 replies

Mark Jongeling
Administrator
Forum|alt.badge.img+23
  • Mark Jongeling
  • Administrator
  • 3945 replies
  • Answer
  • July 21, 2021

Hi Philip,

Indeed, to make the view update successfully you'll need to create an Instead of trigger. 

In the Software Factory, go to Functionality and create a new Control procedure with code group 'INSTEAD_OF_TRIGGERS’. In template, the mutated data will be available through two tables namely inserted and deleted.

Some examples:

Insert:

Show content
insert into table (name)
select i.name
from inserted i

 

Update:

Show content
update t
set name = i.name
from table t
join inserted i
  on i.id = t.id
join deleted d
  on d.id = t.id
where d.name <> 'Admin' --Can't change the name for Admin

 

Delete:

Show content
delete t
from table t
join deleted d
  on d.name = t.name

 

Thereafter assign it to the view on the correct program object (ii for insert, iu for update, id for delete) and you can execute it at Result on the database.

Hope this helps!

"Wake me up before you low-code" | On holiday 06-02 till 27-06
E
1 person likes this
  • E
    Erwin Ekkel
E
Moderator
Forum|alt.badge.img+17
  • Erwin Ekkel
  • Moderator
  • 768 replies
  • July 21, 2021

When working with a view you can only update the data if there is 1 data source (table). In your case with multiple tables an Instead of trigger is the way to go. In the trigger you write an update statement to the base table and feed it with the column info from the view by joining the inserted table . 

P
1 person likes this
  • P
    Philip N
P
Rookie
Forum|alt.badge.img
  • Philip NRookie
  • Author
  • Rookie
  • 4 replies
  • July 21, 2021

Thanks for the answers, i was able to solve it.

Mark Jongeling
E
2 people like this
  • Mark Jongeling
    Mark Jongeling
  • E
    Erwin Ekkel
Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings