Submitted by: kotrc (pkotrc)

Is related to QA172

/*
Problem with

  execute statement 'select c1,c2 from \.\.\.'
  into new\.c1,new\.c2;

in before insert,update triggers.

When new.c1,... has null value before execution,
so execute ... do not fill new.c1,... with value
from select.

Server WI-V2.0.1.12855 Firebird 2.0
System Windows2000 5.00.2195 Service Pack 4
AMD Athalon(TM)

Server WI-V1.5.4.4910 Firebird 1.5
System Windows2000 5.00.2195 Service Pack 4
AMD Athalon(TM)XP 1800+
*/

CREATE EXCEPTION DEXC '???';

CREATE TABLE TEST
(
C1 char(31),
C2 char(31)
);

commit;

SET TERM ^ ;

CREATE TRIGGER TEST_BI FOR TEST
ACTIVE BEFORE INSERT POSITION 20
AS
declare variable sql varchar(30000);
declare variable wrk_c1 char(31);
declare variable wrk_c2 char(31);
BEGIN
/* when new.c1,new.... isn't NULL -> execute statement work ok.
when new.c1,new.c2, is null -> execute statement has some problem */

sql=' select first 1 RDB$CHARACTER_SET_NAME,RDB$DEFAULT_COLLATE_NAME
from RDB$CHARACTER_SETS
where RDB$CHARACTER_SET_NAME is not null';

execute statement sql
into new.c1,new.c2;

select first 1 RDB$CHARACTER_SET_NAME,RDB$DEFAULT_COLLATE_NAME
from RDB$CHARACTER_SETS
where RDB$CHARACTER_SET_NAME is not null
into :wrk_c1,:wrk_c2;

if (wrk_c1 is not null and
new.c1 is null) then
exception dexc 'Is it a bug ? I think yes, it is.';
END
^
set term ; ^

commit;

/* Bug */

insert into test(C1,C2) values (NULL,NULL);

/* OK */
insert into test(C1,C2) values ('A','A');

commit;