Trigger for updatable VIEW can affect on MERGE results

[pavel-zotov]

Perhaps, i misunderstand smth and following works 'as expected', thus sorry for noise. Weird effect can be seen if a VIEW defined as select * from <a_table> has trigger, and we run MERGE into <this_view>. Suppose we have before update trigger (for <this_view>) which assign new values to columns in the same table on which view is based. Further, suppose that code in this trigger contains 'stupid error' in the WHERE-expression: it searches record to be updated using expression like this: where x = old.x order by x rows 1 (rather it should search record by PK; see below line marked as "[ 1 ]").

But either way, the question arises: should changes originated by this trigger be visible to cursor that is used by merge command when it applies to VIEW ? So, consider code:

set bail on;
shell if exist r:\temp\tmp4test.fdb del /q /f r:\temp\tmp4test.fdb;
create database 'localhost:r:\temp\tmp4test.fdb';

create or alter view v1 as select 1 x from rdb$database;
commit;

recreate table t1(id smallint primary key using index t1_id_pk, x smallint, y smallint);
recreate table t2(id smallint primary key using index t2_id_pk, x smallint, y smallint);
commit;

alter view v1 as select * from t1;
commit;

set term ^;
create trigger v1_bu 
inactive -- <<<<<<<<<<<< I.N.A.C.T.I.V.E
before update on v1 as
begin
    update t1 set x = new.x, y = new.y
    -- where id = old.id
    where x = old.x order by x rows 1 -- <<<<<<<<<<<<<<<<<<<<<<<< [ 1 ] <<<<<<<<<<<<<<<<<<<<
    ;
end
^
set term ;^
commit;

-- ########################
-- alter trigger v1_bu active; -- <<<<<<<<<<<<<<<<<< [ 2 ] <<<<<<<<<<<<<<<<<<<<
-- ########################
commit;

insert into t1(id, x, y) values(4, 40, 400);
insert into t1(id, x, y) values(3, 30, 300);
insert into t1(id, x, y) values(1, 10, 100);
insert into t1(id, x, y) values(5, 50, 500);
insert into t1(id, x, y) values(2, 20, 200);

insert into t2(id, x, y) select id,x,y from t1;
commit;

select t.id, t.x, t.y, '<- initial data' as " " from t1 t order by t.id;

-- ############################################
-- ###   m e r g e    i n t o    T A B L E  ###
-- ############################################

set count on;
merge into t1 t
using t2 s
on s.x = t.x 
when matched
then 
    update set
        x = (select max(x) from t1 a where a.x < s.x)
       ,y = (select min(y) from t1 a where a.y > s.y)
;
set count off;

select t.id, t.x, t.y, '<- merged into T1' as " " from t1 t order by t.id;
rollback;

-- ############################################
-- ###   m e r g e    i n t o     V I E W   ###
-- ############################################
set count on;
merge into v1 t
using t2 s
on s.x = t.x 
when matched
then 
    update set
        x = (select max(x) from t1 a where a.x < s.x)
       ,y = (select min(y) from t1 a where a.y > s.y)
;
set count off;

select t.id, t.x, t.y, '<- merged into V1' as " " from t1 t order by t.id;
rollback;

If we run this code right now (i.e. when trigger on view is INactive) then output will be:

     ID       X       Y                 
======= ======= ======= =============== 
      1      10     100 <- initial data 
      2      20     200 <- initial data 
      3      30     300 <- initial data 
      4      40     400 <- initial data 
      5      50     500 <- initial data 

Records affected: 5

     ID       X       Y                   
======= ======= ======= ================= 
      1  <null>     200 <- merged into T1 
      2      10     300 <- merged into T1 
      3      20     400 <- merged into T1 
      4      30     500 <- merged into T1 
      5      40  <null> <- merged into T1 

Records affected: 5

     ID       X       Y                   
======= ======= ======= ================= 
      1  <null>     200 <- merged into V1 
      2      10     300 <- merged into V1 
      3      20     400 <- merged into V1 
      4      30     500 <- merged into V1 
      5      40  <null> <- merged into V1 

But if we uncomment line marked as "[ 2 ]" (-- alter trigger v1_bu active;) then result will differ:

     ID       X       Y                 
======= ======= ======= =============== 
      1      10     100 <- initial data 
      2      20     200 <- initial data 
      3      30     300 <- initial data 
      4      40     400 <- initial data 
      5      50     500 <- initial data 

Records affected: 5

     ID       X       Y                   
======= ======= ======= ================= 
      1  <null>     200 <- merged into T1 
      2      10     300 <- merged into T1 
      3      20     400 <- merged into T1 
      4      30     500 <- merged into T1 
      5      40  <null> <- merged into T1 

Records affected: 5

     ID       X       Y                   
======= ======= ======= ================= 
      1  <null>     200 <- merged into V1 
      2      20     200 <- merged into V1 
      3      30     300 <- merged into V1 
      4      10     300 <- merged into V1 
      5      40  <null> <- merged into V1 

This is difference: