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
Upscene Productions
http://www.upscene.com
http://blog.upscene.com/thomas/

Download LogManager Series, FB TraceManager today!
Continuous Database Monitoring Solutions supporting
Firebird, InterBase, Advantage Database, MS SQL Server
and NexusDB!





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