Hi Andrew,

I just copy/pasted your code step by step. Guess what? Yep, it
worked exactly as expected. After completing the DDL, I inserted one
record: insert into ORDERS (id) values (5). The results:

id = 1 <- yes, it used the generator value

authorid = 1 <- yes, I was logged in as SYSDBA

createdate = 09/05/2002 AM 12.09.57

comment = SYSDBA <- your dummy value

Note that I did remove all your commenting before entering the DDL.
My version of FB is 796.

Why are you using LIKE and not 'where e.login = user'?

Regards,
Andrew Ferguson
-- You say potato, we say starch-based tuber.

--- In ib-support@y..., Andrew Guts <andr@f...> wrote:
> 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