SQL update a read-only view

sql_update_read_only_views.sql

-- Normally you can not update/insert or delete on read only view.
-- And view becomes "read only" if you have any calculation,
-- uses any scalar function or joins
-- 
-- The trick here is to use a "instead-of trigger" where you control 
-- the update, insert and delete process 
-- 
-- Simply paste this gist into ACS SQL and step through the example.
--
-- It is a cool example how far you can go with SQL: Have fun - 
-- (C) Niels Liisberg 2020
--
-- This gist is distributed on an "as is" basis, without warranties 
-- or conditions of any kind, either express or implied.
----------------------------------------------------------------------------------------------

-- Setup the example:
drop schema instof;
create schema instof;
set schema instof;

-- First an ol'school table with classic names:
create or replace table wbusr00P  (
  wbWbTk int,
  wbUser char (10),
  wbName varchar(64)
) on replace delete rows;

-- This view will be read only because of the join: Here we use the list of IBM i users service
create or replace view Web_User as (
    Select 
       wbWbTk as web_user_number,
       wbUser as web_user_id,
       wbName as web_user_name,
       a.*
    from wbusr00P
    left join qsys2.USER_INFO a 
    on wbUser = AUTHORIZATION_NAME
);

-- Does this work .... No !! you will get
-- Message: [SQL0150] View, index, or table WEB_USER in INSTOF read-only
insert into web_user (
    web_user_number, 
    web_user_id, 
    web_user_name
) 
values ( 
    1,
    'DEMO', 
    'John'
);
commit;
   


-- Here we do the magic and implemnts the I/O programatically
create or replace trigger instof.web_user  
    instead of UPDATE or INSERT or DELETE on web_user 
    referencing  NEW AS new_row OLD as old_row
for each row mode DB2ROW
    set option output=*print, commit=*none, dbgview = *source --list
begin 
    
    if UPDATING then 
        update wbusr00P 
        set  wbWbTk = new_row.web_user_number,
             wbUser = new_row.web_user_id,
             wbName = new_row.web_user_name
        where wbWbTk = new_row.web_user_number;
           
    elseif INSERTING then
        insert into wbusr00P ( 
            wbWbTk, 
            wbUser,
            wbName
        ) 
        values (
            new_row.web_user_number,
            new_row.web_user_id,
            new_row.web_user_name
        );

    elseif DELETING then
        delete from wbusr00P 
        where wbWbTk = old_row.web_user_number;
    end if;
end;


-- Now try again with an insert:
insert into web_user (
    web_user_number, 
    web_user_id, 
    web_user_name
) 
values ( 
    1,
    'DEMO', 
    'John'
);
commit;

-- does that work? Wow!! Sure
select  * from web_user;

-- How about update ?  
update web_user 
set web_user_name = 'Johnny'
where web_user_number = 1;
commit;

-- Brilliant !!
select  * from web_user;

-- Delete ? 
delete from web_user 
where web_user_number = 1;
commit;

select  * from web_user;
-- Every thing works !!