Hi all

Please, help me with that!

My tables are (simplified):

create table Employees (
id integer not null primary key,
login varchar(30) not null,
...
);

create unique index idx_Emp_login on Employees (login);

insert into Employees (ID, Login) values (1, 'SYSDBA');
....

create table Orders (
id integer not null primary key,
AuthorID integer references Employees (id),
CreateDate TIMESTAMP not null,
Comment varchar(250)
);

create generator G_ORD_ID;

And problematic trigger:

set term ^ ;
create trigger On_Add_Order for orders before insert as
DECLARE VARIABLE AUTH INTEGER;
begin
NEW.ID = GEN_ID(G_ORD_ID, 1);
NEW.CREATEDATE = 'NOW';

// line below was added for debug purpose only
NEW.COMMENT = USER; // or CURRENT_USER, it doesn't matter

SELECT E.ID from Employees E where E.Login LIKE USER into AUTH;
/* This SELECT returns 0 rows, and so AUTH remains NULL */
/* The same SELECT, but without "INTO" running from console returns
one row as expected*/
NEW.AUTHORID = AUTH;
end^
set term ; ^

Inserted record looks like:
ID = <not-empty value>
AuthorID = NULL
CreateDate = <not-empty value>
Comment = 'SYSDBA'

The question is: Why AthorID remains NULL ?

Thanks in advance.

Andrew