Hello Olaf

Please do not top post! Look at the end of the message ->

>
> Hello again,
>
> I'm sorry, my mistake.
>
> One condition makes me trouble with NULL as you thought. The "Bemerkung" -
> comment-field was set to null as default value. Now, I change the value

and

> old.value <> new.value doesn't work, because the old value is NULL. After

I

> change the value again, it works.
>
> Now, I have added the follow condition:
>
> If(old.comment<>new.comment) or ((old.comment is null)and(new.comment is

not

> null))
>
> Is there a better way to compare?
>
> Thanks Thomas, sorry for my mistake
>
> ____________________
>
> Hello Thomas,
>
> yes, it was bad formatted! Here the original view:
> http://www.pusmetall.de/trigger.JPG
>
> The begin-end-block also for one statement is more clearly, you're right.
> The problem, it is not required, such as in c++ (more than one statement -
> {})
>
> The most fields I compare are required an cannot be NULL, therefore not

the

> comparison with NULL. Other values were generated on before insert/before
> update triggers and set if fails. The result of the trigger should be
> generating/updating a job for surface coating (powder coating). The

hang-on

> of the products is planned by the controller in required sequence . Only

by

> this work-step on this workstation it will be automatically creating a
> second job for decrease the products after powder coating, because the
> decrease-step is identical - only delayed - but the same sequence. And the
> controller does not want to plan both identical steps. So I have created

an

> insert, update and delete trigger.
>
> Okay, in other procedures/trigger I'll set a NULL value to 0 (if integer)
> and compare to null of course!
>
> My problem in this case, one complete line of code was not used. (more

than

> one time compiled) After I have changed the line break and set it again on
> the same position, it works fine!? I don't know why! Can it be a problem

of

> the ems ib/fb-manager?
>
> Thank you.
>
> _______________
>
> > i have created a trigger. The source code below.
> >
> >
> >
> > CREATE TRIGGER TPLAN_KW_POS_AU FOR TPLAN_KW_POS ACTIVE
> >
> > AFTER UPDATE POSITION 0
> >
> > AS
> >
> > declare variable apnr integer;
> >
> > declare variable nextag integer;
> >
> > declare variable mengeh double precision;
> >
> > BEGIN
> >
> > if((old.ap = 69)and(new.ap<> 69)) then /* ehemals Pulverei */
> >
> > delete from tplan_kw_pos where id_autoins = old.id;
> >
> > if((old.ap<> 69)and(new.ap = 69)) then /* neu jetzt Pulverei */
> >
> > begin
> >
> > select arbeitsplatznr, stck_je_std from tarbg where teilenr =
> > new.teilenr and bsnr = new.arbgang+1 into :apnr, mengeh;
> >
> > if((apnr = 68) or (apnr = 67)) then
> >
> > insert into tplan_kw_pos(ltjahr, ltkw, auftragsjahr, auftragsnr, kwtag,
> > stck_je_std, stck_je_tag, teilenr, arbgang,
> >
> > ap, abgearbeitet, ps2, id_autoins, prio) values
> >
> > (new.ltjahr, new.ltkw, new.auftragsjahr, new.auftragsnr, new.kwtag,
> > :mengeh, new.stck_je_tag, new.teilenr, new.arbgang+1,
> >
> > :apnr, 0, 0, new.id, new.prio);
> >
> > end
> >
> > if((new.ap = 69)and(old.ap = 69)) then /* unverndert Pulverei */
> >
> > begin
> >
> > if((new.teilenr<> old.teilenr)or(new.arbgang<> old.arbgang)or
> >
> > (new.auftragsjahr<> old.auftragsjahr)or(new.auftragsnr<>
> > old.auftragsnr)) then
> >
> > begin /* Teil, AG, Auftragsjahr, Auftragsnr hat/haben sich gendert,
> > Datensatz lschen und neu anlegen*/
> >
> > delete from tplan_kw_pos where id_autoins = old.id;
> >
> > select arbeitsplatznr, stck_je_std from tarbg where teilenr =
> > new.teilenr and bsnr = new.arbgang+1 into :apnr, mengeh;
> >
> > if((apnr = 68) or (apnr = 67)) then
> >
> > insert into tplan_kw_pos(ltjahr, ltkw, auftragsjahr, auftragsnr,
> > kwtag, stck_je_std, stck_je_tag, teilenr, arbgang,
> >
> > ap, abgearbeitet, ps2, id_autoins, prio) values
> >
> > (new.ltjahr, new.ltkw, new.auftragsjahr, new.auftragsnr, new.kwtag,
> > :mengeh, new.stck_je_tag, new.teilenr, new.arbgang+1,
> >
> > :apnr, 0, 0, new.id, new.prio);
> >
> > end
> >
> > else if((new.ltjahr<> old.ltjahr)or(new.ltkw<> old.ltkw)or(new.kwtag
> > <> old.kwtag)or(new.prio<> old.prio)or
> >
> > (new.bemerkung<> old.bemerkung)or(new.stck_je_tag<>
> > old.stck_je_tag))then
> >
> > begin /* Aktualisierung der Werte */
> >
> > update tplan_kw_pos set ltjahr = new.ltjahr, ltkw = new.ltkw,
> >
> > kwtag = new.kwtag, prio = new.prio, bemerkung = new.bemerkung,
> > stck_je_tag = new.stck_je_tag
> >
> > where id_autoins = new.id;
> >
> > end
> >
> > end
> >
> > END
> >
> >
> >
> > After the position ".<> old.prio)or" there is a line break:
> >
> > else if((new.ltjahr<> old.ltjahr)or(new.ltkw<> old.ltkw)or(new.kwtag
> > <> old.kwtag)or(new.prio<> old.prio)or
> >
> > (new.bemerkung<> old.bemerkung)or(new.stck_je_tag<>
> > old.stck_je_tag))then
> >
> > I'm frustrated, but the condition on the next line was not observed!

When

> I
> > change the values (ltjahr, ltkw . prio), the instructions will be
> executed!
> > But If I change the "bemerkung", nothing happened! But after I have
> removed
> > the line break and set it new, all conditions were taken! It's crazy!
> >
> >
> >
> > I use the ems firebird interbase manager and firebird 2.1.1.17.. What

can

> be
> > the problem?
> >
>
> Having a hard time to read your code in respect to formatting. My number
> one rule is always use begin ... end blocks for IF etc., even if there
> is only one statement involved. Just in case.
>
> Any chance that one of your values might contain NULL, because a
> difference check using the comparison operator fails with NULL. You'd
> better use the IS DISTINCT FROM clause instead of <> for such checks.
>
> --
> With regards,
>
> Thomas Steinmaurer

Here Thomas has mentioned already IS DISTINCT FROM ;-)

Cheers
Christian

Hi there,

I should complete read the renewals of firebird 2! There are much new
functions integrated, nice features! (I started with firebird 1.5)

Quickly I have read "distinct" and immediately I thought of "select distinct
." But now, I'm excited!

Thank you!







[Non-text portions of this message have been removed]