Create Trigger MySql update or insert in another table

Question

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.

Answer 1

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 and AFTER INSERT:

AFTER INSERT:

USE `TEST`;
DELIMITER $$
DROP TRIGGER IF EXISTS MEDICAMENTO.monthUpdateTriggerAI$$
USE `TEST`$$
 CREATE DEFINER = CURRENT_USER TRIGGER `TEST`.`monthUpdateTriggerAI` AFTER INSERT ON `TestTable` FOR EACH ROW
 BEGIN
 SET @COUNT=(SELECT COUNT(*) FROM TestTable2 WHERE (ItemId=NEW.ItemId AND Year=YEAR(NEW.Date) AND Month=MONTH(NEW.Date)));
 IF @COUNT=0 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 TestTable2.Close=NEW.Close AND Increase=(NEW.Close/(SELECT 
 Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)) LIMIT 1))
 WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date));
 END IF;
 END;
$$
DELIMITER ;

AFTER UPDATE:

USE `TEST`;
DELIMITER $$
DROP TRIGGER IF EXISTS MEDICAMENTO.monthUpdateTriggerAU$$
USE `TEST`$$
 CREATE DEFINER = CURRENT_USER TRIGGER `TEST`.`monthUpdateTriggerAU` AFTER UPDATE ON `TestTable` FOR EACH ROW
BEGIN
 UPDATE TestTable2 SET TestTable2.Close=NEW.Close AND Increase=(NEW.Close/(SELECT 
 Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)) LIMIT 1))
 WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date));
END;
$$
DELIMITER ;

Note: Be careful about the SELECT Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)), I've added a LIMIT 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 .