this is not OK -------------------------------- :::::
SELECT E.ID from Employees E where E.Login LIKE USER into AUTH;

better SELECT E.ID from Employees E where E.Login LIKE "USER%" into AUTH;
.... or better.... "...e.login= USER ..."

kind regds

Claus


Andrew Guts 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
>
>
>
>
>To unsubscribe from this group, send an email to:
>ib-support-unsubscribe@egroups.com
>
>
>
>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>
>.
>