Thank you for your help. Now I have rewritten the trigger and the compilation
works. But I recieved always an exception. So I removed line by line to find
where the error. At least it was a small trigger:

IF (EXISTS(SELECT PERSONENID FROM ADRESSE
WHERE PERSONENID = NEW.PERSONENID AND ADRESSENTYP = NEW.ADRESSENTYP
AND (GUELTIGVON IS NULL AND GUELTIGBIS IS NULL)
OR (GUELTIGVON IS NULL AND NEW.GUELTIGVON IS NULL)
OR (GUELTIGBIS IS NULL AND NEW.GUELTIGBIS IS NULL))) THEN
EXCEPTION ADRESSE_UNGUELTIG;

If I have some expressions between parenthesis, combined with and, than the
whole block is only true, if all expressions are true. Is it right?
I have:
GUELTIGVON is Null
GUELTIGBIS = 20.12.2002
NEW.GUELTIGVON = 21.12.2002
NEW.GUELTIGBIS is Null
Why goes the trigger to exception? If I remove the last expression (GUELTIGBIS
IS NULL AND NEW.GUELTIGBIS IS NULL), then it don't go to exception. Is the
problem with the Null-State? Or with the parenthesis?

Thank you for your advise.

Walter Neumann

>
> The reason it doesn't work is that triggers are server-side code modules
> which are compiled at creation time, not at run-time. Any queries which
> you perform in triggers must be resolvable at compile time.
>
> Additionally, a trigger is row-specific, so you need to be careful about
> context when querying the same table from within an insert operation. I
> suggest that you review the logic of the checks you want to perform and use
> the EXISTS(..) predicate to pre-set your exception conditions. Use a
> table alias when referring to the table.
>
> The following "nonsense" statement might help to get you started if you
> haven't encountered EXISTS(..) before:
>
> IF (EXISTS (SELECT mich.AFIELD FROM ADRESSE mich
> WHERE mich.ACOL = NEW.ACOL
> OR (mich.ACOL IS NOT NULL AND mich.BCOL=NEW.BCOL) ))
> THEN EXCEPTION ADRESS_UNGUELTIG;
>
> If your logic needs it, NOT EXISTS is also valid, e.g.
>
> IF (NOT EXISTS (SELECT mich.AFIELD FROM ADRESSE mich
> WHERE mich.ACOL = NEW.ACOL
> OR (mich.ACOL IS NOT NULL AND mich.BCOL=NEW.BCOL) ))
> THEN EXCEPTION ADRESS_UNGUELTIG;
>
> heLen
>
>
>
> 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/