Re: TRIGGER question

[shannon allen]

While still trying to solve this problem I've changed my approach slightly by using a stored procedure.

mysql> delimiter $
mysql> CREATE PROCEDURE find_plat
-> (plat INT, OUT found INT)
-> BEGIN
-> DECLARE count CURSOR FOR
-> SELECT PlatformId from PositionChangedTable WHERE PlatformId=plat;
-> OPEN count;
-> FETCH count INTO found;
-> close count;
-> END $

This procedure works when called from the mysql prompt.
I now try to call it from within my trigger:

mysql> delimiter $
mysql> CREATE TRIGGER t_poschanged
-> AFTER INSERT on Positiontable
-> For Each Row
-> Call find_plat(New.PlatformId,@found);
-> if (@found=0) then
-> insert into positionchangedtable values(NULL,NEW.PlatformId,NEW.Time);
-> else
-> update positionchangedtable SET Id=NULL, Time=NEW.Time;
-> end if;
-> END $

but get this errror:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if (@
found=0) then
insert into positionchangedtable values(NULL,NEW.Platfo' at line 1

Any ideas??