Trigger Problems

[Andreas Baderke]

sorry but me english is not so good...
i have a trigger but is doesn't work
why?


my trigger:

DROP TRIGGER IF EXISTS `InsUser`;

DELIMITER $$
CREATE TRIGGER InsUser AFTER INSERT ON marktplatz_user
FOR EACH ROW BEGIN
SET @name = NEW.name;
SET @incomesum = NEW.incomesum;
SET @user_id = NEW.id;
SET @aktdatum = CURDATE();
CASE WHEN
(SELECT id FROM marktplatz_weekdata WHERE user_id = @user_id and datum = @aktdatum) IS NULL
THEN
INSERT INTO minecraft.marktplatz_weekdata ( user_id, datum, name, currentmoney) VALUES (@user_id, @aktdatum, @name, @incomesum);
END IF;
END;
$$

the error:
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 ') IS NULL
THEN
SERT INTO minecraft.marktplatz_weekdata ( user_id, datum, name, c' at line 8


can anybody help me?
i have: debian and the mysql server version is: 5.1.66


UPDATE:
DROP TRIGGER IF EXISTS `InsUser`;

DELIMITER $$
CREATE TRIGGER InsUser AFTER INSERT ON marktplatz_user
FOR EACH ROW BEGIN
SET @name = NEW.name;
SET @incomesum = NEW.incomesum;
SET @user_id = NEW.id;
SET @aktdatum = CURDATE();
SET @aktid = (SELECT id FROM marktplatz_weekdata WHERE user_id = @user_id and datum = @aktdatum LIMIT 1);
CASE WHEN
SELECT @aktid IS NULL;
THEN
INSERT INTO marktplatz_weekdata ( user_id, datum, name, currentmoney) VALUES (@user_id, @aktdatum, @name, @incomesum);
END CASE;
END;
$$

that is the new version but i get an error on the insert statment:
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 '; THEN SERT INTO marktplatz_weekdata ( user_id, datum, name, currentmoney) VALUE' at line 1



update:
SQL-Befehl: Dokumentation

DELIMITER $$ CREATE TRIGGER InsUser AFTER INSERT ON marktplatz_user
FOR EACH
ROW BEGIN
SET @name = NEW.name;
SET @incomesum = NEW.incomesum;
SET @user_id = NEW.id;
SET @aktdatum = CURDATE( ) ;

CASE WHEN
SELECT id FROM marktplatz_weekdata
WHERE user_id = @user_id
AND datum = @aktdatum
LIMIT 1 IS NULL ;

THEN INSERT INTO marktplatz_weekdata( id, user_id, datum, name, currentmoney )
VALUES (NULL , @user_id , @aktdatum , @name , @incomesum);

END CASE ;
END ;
$$

MySQL meldet: Dokumentation
#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 'SELECT id FROM marktplatz_weekdata WHERE user_id = @user_id and datum = @aktdatu' at line 8



Edited 6 time(s). Last edit at 08/14/2013 12:34AM by Andreas Baderke.