MySQL Forums
Forum List  »  Delphi

Trigger Problems
Posted by: Andreas Baderke
Date: August 13, 2013 04:15PM

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.

Options: ReplyQuote


Subject
Written By
Posted
Trigger Problems
August 13, 2013 04:15PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.