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.
Best Answer
First, you need to specified when do you want to run the trigger inside the table. I've edited your trigger and use
AFTER UPDATE
andAFTER INSERT
:AFTER INSERT:
AFTER UPDATE:
Note: Be careful about the
SELECT Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date))
, I've added aLIMIT 1
to avoid duplicate rows.Change the database
TEST
.You can also use
ON DUPLICATE KEY
on the two triggers execution.You can get more information in the MySQL's documentation manual.