Solved

Editable view

  • 21 July 2021
  • 3 replies
  • 176 views
P
Rank
Badge

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.

 

 

 

 

icon

Best answer by Mark Jongeling 21 July 2021, 13:14

View original

3 replies

Mark Jongeling
Rank
Userlevel 7
Badge +23

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:

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

 

Update:

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:

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"
E
Rank
Userlevel 6
Badge +16

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
Rank
Badge

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

Reply


Terms & ConditionsCookie settings