At 06:44 PM 16-12-02 +0100, you wrote:

>Hi all,
>I am a Newbie in Database. I would like to check with a trigger before
>insert,
>if the new row has collisions wither other existing rows. I have to check 4
>columns: All rows with the same ID and the same typ must be checked, if the
>starting date and the ending date cross the new dates. The date-fields could
>be null, what means there is no starting or ending date. I tried the
>following, but it doesent work:
>
>DECLARE VARIABLE SEL_STRING;
>BEGIN
> IF (NEW.GUELTIGVON IS NULL) AND (NEW.GUELTIGBIS IS NOT NULL) THEN
> SEL_STRING = ' AND GUELTIGVON IS NULL OR GUELTIGVON <= NEW.GUELTIGBIS'
> ELSE IF (NEW.GUELTIGVON IS NOT NULL) AND (NEW.GUELTIGBIS >=
> NEW.GUELTIGVON)
>THEN
> SEL_STRING = ' AND GUELTIGBIS IS NULL OR GUELTIGBIS >= NEW.GUELTIGVON'
> ELSE IF (NEW.GUELTIGVON IS NOT NULL) AND (NEW.GUELTIGBIS IS NOT NULL)
> THEN
> SEL_STRING = ' AND GUELTIGVON IS NULL OR GUELTIGBIS IS NULL OR
> (GUELTIGVON >= NEW.GUELTIGVON AND GUELTIGVON <= NEW.GUELTIGBIS) OR
> (GUELTIGBIS <= NEW.GUELTIGBIS AND GUELTIGBIS >= NEW.GUELTIGVON) OR
> (GUELTIGVON < NEW.GUELTIGVON AND GUELTIGBIS > NEW.GUELTIGBIS)';
> SELECT PERSONENID FROM ADRESSE WHERE PERSONENID = NEW.PERSONENID
> AND ADRESSENTYP = NEW.ADRESSENTYP||SEL_STRING;
> IF PERSONENID > 0 THEN
> EXCEPTION ADRESS_UNGUELTIG;
>END
>
>Can anyone help me?

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