trigger won't work

[Charlie Schaubmair]

Hi,

I've done a new trigger:
[pre]
DELIMITER $$;

DROP TRIGGER `myDB`.`createNewDataField`$$

CREATE TRIGGER `myDB`.`createNewDataField` AFTER INSERT on `myDB`.`rel_formsitegroups_spacefields`
FOR EACH ROW BEGIN
DECLARE NEW_form_id INT;
DECLARE NEW_rID INT;
DECLARE NEW_shopID INT;
DECLARE cnt INT;
DECLARE done INT DEFAULT 0;
SET NEW_rID = NEW.id;
IF NEW.form_site_groups_id > 0 THEN
SELECT
fs.form_id INTO NEW_form_id
FROM
rel_formsitegroups_spacefields r
LEFT OUTER JOIN
tbl_form_site_groups g
ON
r.form_site_groups_id = g.id
LEFT OUTER JOIN
tbl_form_sites fs
ON
g.form_site_id = fs.id
WHERE
r.id = NEW_rID;

DECLARE mycursor CURSOR FOR SELECT id FROM tbl_datas_head WHERE form_id = NEW_form_id;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
SELECT COUNT(id) FROM tbl_datas_head WHERE form_id = NEW_form_id;
OPEN mycursor;
myloop:LOOP
FETCH mycursor INTO NEW_shopID;
IF done=1 THEN LEAVE myloop; END IF;

INSERT INTO
tbl_datas
(shopID, rID, fieldValue)
VALUES
(NEW_shopID, NEW_rID, '');

END LOOP myloop;
END IF;
END;
$$

DELIMITER ;$$
[/pre]
BUT I always get the error:
Error Code : 1064
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 'DECLARE mycursor CURSOR FOR SELECT id FROM tbl_datas_head WHERE form_id = NEW_fo' at line 11
(0 ms taken)

Please help.

thx+br
Charlie



Edited 1 time(s). Last edit at 08/08/2006 01:57AM by Charlie Schaubmair.