I am trying to learn more about triggers in mysql, so bear with me. I have two tables,
TestTable
(id INTEGER PRIMARY KEY AUTO_INCREMENT, ItemId INTEGER,Date date, Value REAL)
TestTable2
(id INTEGER PRIMARY KEY AUTO_INCREMENT, ItemId INTEGER,Year INTEGER, Month INTEGER, Open REAL, Close REAL, Increase REAL)
Where the table TestTable is measurements on a certain item on a specific date. TestTable2 contains measurements for a specific month,year and the increase during this period.
I want to create a trigger which updates or insert values into TestTable2 as I insert values into TestTable.
This is what the trigger i tried to create looked like,
CREATE TRIGGER 'monthUpdateTrigger' AFTER INSERT ON TestTable
BEGIN
IF NOT (EXISTS (SELECT 1 FROM TestTable2 WHERE
(ItemId=NEW.ItemId AND Year=YEAR(NEW.Date) AND Month=MONTH(NEW.Date))))
THEN
INSERT INTO TestTable2 (ItemId,Year,Month,Open,Close,Increase ) VALUES
(NEW.ItemId , YEAR(NEW.Date), MONTH(NEW.Date),NEW.Value,NEW.Close,0.0);
ELSE
UPDATE TestTable2 SET Close=NEW.Close AND Increase=(NEW.Close/(SELECT
Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)))
WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date))
END IF;
END;
But this does not seem to work, I get the error,
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 'BEGIN IF NOT (EXISTS (SELECT 1 FROM TestTable WHERE (ItemId=NEW.ItemId AND Yea' at line 1
Can someone please point out the error/errors. Or offer a better solution to this problem.