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.