Trigger Error 1064

[Chirag Fisher]

animaldetails
Animal_Id int (primary key)
IsMilking varchar (Yes/NO)
Milking_Capacity varchar

milking
Animal_Id int (primary key)
MonthYear varcahr (eg. '6 2009' -- June 2009)
Day1 varchar
Day2 varchar
and so on (till 31)


milkingevent
TrigPoint int (primary key)


Requirements:

1. I require a trigger for table3 After update.
Loop 1
2. In table 1 it will search in each row for attribute IsMilking if it is YES it will store value of Milking_Capacity in an variable1 as well as Animal_Id in another variable2.
Loop2
3. Now it should go to table 2 search for Animal_Id from variable2. and Match with current month and year
4.
* If no record is found INSERT statement "Insert into table2..." values (variable2, current(month and Year), { eg. if to days date is 1 then value of variable1 in Day1 and reset all others to default value)
* If record exists in milking table, Update statement "Update into table2 ..." (where if current date is 28 then set value in Day 28 reset should be as it is.....)
5. After it gets completed Go to loop 1 and search for Second animal ID and the process continues.


MYSQL:

CREATE TRIGGER TBU_milkingevent After Update ON milkingevent
FOR EACH ROW BEGIN
DECLARE @_lAnimal_Idint INT DEFAULT 0;
DECLARE @_lIsMilking INT DEFAULT 0;
DECLARE @_lMilking_Capacity VARCHAR(50) DEFAULT "";
DECLARE @_lMonth_Year VARCHAR(50) DEFAULT "";
DECLARE @_lCount INT DEFAULT 0;
DECLARE @_lAnimal_Id INT DEFAULT 0;

DECLARE Table1_CUR CURSOR FOR SELECT Milk_Capacity,Animal_Id FROM animaldetails WHERE IsMilking= 'Yes';
OPEN Table1_CUR;
FETCH Table1_CUR INTO @_lMilking_Capacity,@_lAnimal_Idint;

    BEGIN
            SET @_lMonth_Year= SELECT  CONCAT(MONTH(current_date), ' ',YEAR(current_date));
            SET @_lCount = Select COUNT(*) FROM milking WHERE Animal_Id=@_lAnimal_Idint AND MonthYear=@_lMonth_Year;
            IF @_lCount>0
        BEGIN
            IF DAY(current_date)=30
                BEGIN
                    Update milking
                    SET Day26=@_lMilking_Capacity
                END
            ELSE
                BEGIN
                        IF DAY(current_date)=30
                            BEGIN
                                INSERT INTO milking (Animal_Id,MonthYear, Day26) VALUES (@_lAnimal_Idint, @_lMonth_Year,@_lMilking_Capacity)
                            END
                END
           END
            FETCH Table1_CUR INTO @_lMilking_Capacity,@_lAnimal_Idint
    END
CLOSE Table1_CUR;

END

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++++++++++++++
ERROR DISPALYED ARE
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++++++++++++++
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 '@_lAnimal_Idint INT' at line 3
(0 ms taken)

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 @_lIsMilking INTEGER DEFAULT 0' at line 1
(0 ms taken)

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 @_lMilking_Capacity VARCHAR(50) DEFAULT ""' at line 1
(0 ms taken)

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 @_lMonth_Year VARCHAR(50) DEFAULT ""' at line 1
(0 ms taken)

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 @_lCount INTEGER DEFAULT 0' at line 1
(0 ms taken)

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 @_lAnimal_Id INTEGER DEFAULT 0' at line 1
(0 ms taken)

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 Table1_CUR CURSOR FOR SELECT Milk_Capacity,Animal_Id FROM animaldetails ' at line 1
(0 ms taken)

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 'OPEN Table1_CUR' at line 1
(0 ms taken)

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 'FETCH Table1_CUR INTO @_lMilking_Capacity,@_lAnimal_Idint' at line 1
(0 ms taken)

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 'SET @_lMonth_Year= SELECT CONCAT(MONTH(current_date) ,' ', YEAR(current_date))' at line 2
(0 ms taken)

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 'Select COUNT(*) FROM milking WHERE Animal_Id=@_lAnimal_Idint AND MonthYear=@_lMo' at line 1
(0 ms taken)

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 'IF @_lCount>0
BEGIN
IF (DAY(current_date)='26')
BEGIN
Update milking' at line 1
(0 ms taken)

[guelphdad]

Look up database normalization, whenever you store columns like Day1, Day2, Day3 ... then your data is most likely not normalized.

when you normalize your data, you'll see it is much easier to track and query your data.