Hi,
I have written several triggers for a number of tables, all working fine
except for two. These 2 triggers are update triggers that compare current
contents with new contents (ie __old vs __new). On both these triggers,
when I hit the verify syntax button on the triggers window I get the
following error: Error 7200: AQE Error: State = S0000; NativeError = 2132;
[Extended Systems][Advantage SQL Engine] Table alias used multiple times:
MWPAT

Even though I get the error message, on testing, the triggers seem to
function as they should. I'm guessing I may be using the __old vs __new
incorrectly since this didn't start until I added that logic, but I can't
find anything in the documentation that says so and the error message and
native error descriptions aren't helping me much.

The problem triggers take the following form:

CREATE TRIGGER patupdtrg
ON MWPAT
AFTER
UPDATE
BEGIN
insert into ehrpat (field1,field2,.....)
select fielda,fieldb,.....
from __new inner join __old on __new.[chart number] = __old.[chart number]
where __new.[last name] <> __old.[last name] or
(__new.[last name] is not null and __old.[last name] is null) or
__new.[first name] <> __old.[first name] or
(__new.[first name] is not null and __old.[first name] is null) or
__new.[middle initial] <> __old.[middle initial] or
(__new.[middle initial] is not null and __old.[middle initial] is null) or
__new.[street 1] <> __old.[street 1] or
(__new.[street 1] is not null and __old.[street 1] is null) or
__new.[street 2] <> __old.[street 2] or
(__new.[street 2] is not null and __old.[street 2] is null) or
__new.city <> __old.city or
(__new.city is not null and __old.city is null) or
__new.state <> __old.state or
(__new.state is not null and __old.state is null) or
__new.[zip code] <> __old.[zip code] or
(__new.[zip code] is not null and __old.[zip code] is null) or
__new.country <> __old.country or
(__new.country is not null and __old.country is null) or
__new.[date of birth] <> __old.[date of birth] or
(__new.[date of birth] is not null and __old.[date of birth] is null) or
__new.[social security number] <> __old.[social security number] or
(__new.[social security number] is not null and __old.[social security
number] is null) or
__new.sex <> __old.sex or
(__new.sex is not null and __old.sex is null) or
__new.email <> __old.email or
(__new.email is not null and __old.email is null) or
__new.[work extension] <> __old.[work extension] or
(__new.[work extension] is not null and __old.[work extension] is null) or
__new.inactive <> __old.inactive or
(__new.inactive is not null and __old.inactive is null) or
__new.[assigned provider] <> __old.[assigned provider] or
(__new.[assigned provider] is not null and __old.[assigned provider] is
null) or
__new.[contact name] <> __old.[contact name] or
(__new.[contact name] is not null and __old.[contact name] is null) or
__new.[phone 1] <> __old.[phone 1] or
(__new.[phone 1] is not null and __old.[phone 1] is null) or
__new.[work phone] <> __old.[work phone] or
(__new.[work phone] is not null and __old.[work phone] is null) or
__new.[phone 4] <> __old.[phone 4] or
(__new.[phone 4] is not null and __old.[phone 4] is null) or
__new.[contact phone 1] <> __old.[contact phone 1] or
(__new.[contact phone 1] is not null and __old.[contact phone 1] is null);

END
PRIORITY 2;