Re: Trigger erroring on a simple select count(*) statement: may be to do with variables

[Jennifer Andres]

The missing "then" was the problem - all the rest was just desperate attempts to get the thing to work!

Thank you so much!
Jennifer


This worked:

create trigger TT_AddParent_up_be before update on TEMPKW
for each row
trig: BEGIN
declare KWSYNONYM integer default old.keywordid;
declare TEMPKWSYN integer default old.keywordid;
declare PSYNONYM integer default old.parentid;
declare TEMPPSYN integer default old.parentid;
declare HIERCOUNT numeric default 0;
#declare OLDKW integer default OLD.KeywordID;
#declare OLDPID integer default OLD.ParentID;
#declare NEWPID integer default NEW.ParentID;

if OLD.ParentID = NEW.ParentID
then leave trig;
end if;

create temporary table if not exists TTAPUP like tempkh;
select min(equalitykeyword) into TEMPKWSYN from tempke where (keywordid=old.keywordid or equalitykeyword=old.keywordID);
select min(equalitykeyword) into TEMPPSYN from tempke where (keywordid=NEW.ParentID or equalitykeyword=NEW.ParentID);

select ifnull(tempkwsyn, old.keywordID) into KWSYNONYM;
select ifnull(temppsyn, new.parentID) into PSYNONYM;

if KWSYNONYM <> old.keywordID
then
select count(*) into hiercount from tempkh where keywordid=KWSYNONYM;
end if;
if @HIERCOUNT > 1
then set NEW.ParentID := 0;
leave trig;
end if;

insert ignore into TTAPUP (select KWSYNONYM as kw, parentkeyword from tempkh where keywordid=PSYNONYM);
insert into TEMPKH select * from TTAPUP where keywordid=KWSYNONYM;
drop temporary table TTAPUP;
END trig;