csswa wrote:

> 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.

I use Firebird 1.0 SS build 796 32bit IO under RedHat 7.2.
File: FirebirdSS-1.0.0.796-0.i386.rpm 2704315 bytes
But doesn't work as expected :-(

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

Because I've tried '=' at first with same result. I prefer '=' here of
course.
Storing USER to local variable before SELECT helped me.

Thank you.

>
>
> 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
>
>
> 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/