Hello Emil,

> SQL> Create Database 'test.fdb' user 'sysdba' password 'masterkey';
> SQL>
> SQL> create table T (ID integer);
> SQL>
> SQL> create view V as select ID from T;
> SQL>
> SQL> create sequence SEQ;
> SQL>
> SQL> set term ^;
> SQL>
> SQL> create trigger T_BI for T before insert as
> CON> begin
> CON> new.ID = next value for SEQ;
> CON> end^

It is often more useful to define the BI trigger as:

create trigger T_BI for T before insert as
begin
if (new.ID is null) then new.ID = next value for SEQ;
end^

(but if you had done that, you wouldn't have detected the inconsistency below ;-))

> SQL>
> SQL> create trigger V_BI for V before insert as
> CON> begin
> CON> insert into T values (new.ID);
> CON> end^
> SQL>
> SQL> set term ; ^
> SQL>
> SQL> insert into T values (9) returning ID;
>
> ID
> ============
> 1
>
> SQL>
> SQL> insert into V values (101) returning ID;
>
> ID
> ============
> 101

I would consider this a bug in the RETURNING clause, which should reflect changes made by BEFORE triggers. Of course, new.ID as seen from the *view* trigger does not change, so this may be tricky to detect automatically. But as a database designer, you could and should do this in the view trigger:

insert into T values (new.ID) returning T.id into :new.id;

That would solve the whole problem (he said without having tested it).

>
> SQL> select * from V;
>
> ID
> ============
> 1
> 2

This is correct.


Cheers,
Paul Vinkenoog