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
