Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)) LIMIT 1)) UPDATE TestTable2 SET TestTable2.Close=NEW.Close AND Increase=(NEW.Close/(SELECT SET COUNT(*) FROM TestTable2 WHERE (ItemId=NEW.ItemId AND Year=YEAR(NEW.Date) AND Month=MONTH(NEW.Date))) I've edited your trigger and use AFTER UPDATE and AFTER INSERT: AFTER INSERT: USE `TEST` ĭROP TRIGGER IF EXISTS MEDICAMENTO.monthUpdateTriggerAI$$ĬREATE DEFINER = CURRENT_USER TRIGGER `TEST`.`monthUpdateTriggerAI` AFTER INSERT ON `TestTable` FOR EACH ROW Or offer a better solution to this problem.įirst, you need to specified when do you want to run the trigger inside the table. Near 'BEGIN IF NOT (EXISTS (SELECT 1 FROM TestTable WHEREĬan someone please point out the error/errors. You have an error in your SQL syntax check the manual thatĬorresponds to your MySQL server version for the right syntax to use WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date))īut this does not seem to work, I get the error, ![]() Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date))) UPDATE TestTable2 SET Close=NEW.Close AND Increase=(NEW.Close/(SELECT (NEW.ItemId, YEAR(NEW.Date), MONTH(NEW.Date),NEW.Value,NEW.Close,0.0) INSERT INTO TestTable2 (ItemId,Year,Month,Open,Close,Increase ) VALUES (ItemId=NEW.ItemId AND Year=YEAR(NEW.Date) AND Month=MONTH(NEW.Date)))) IF NOT (EXISTS (SELECT 1 FROM TestTable2 WHERE This is what the trigger i tried to create looked like, CREATE TRIGGER 'monthUpdateTrigger' AFTER INSERT ON TestTable I want to create a trigger which updates or insert values into TestTable2 as I insert values into TestTable. TestTable2 contains measurements for a specific month,year and the increase during this period. Where the table TestTable is measurements on a certain item on a specific date. ![]() (id INTEGER PRIMARY KEY AUTO_INCREMENT, ItemId INTEGER,Year INTEGER, Month INTEGER, Open REAL, Close REAL, Increase REAL) ![]() (id INTEGER PRIMARY KEY AUTO_INCREMENT, ItemId INTEGER,Date date, Value REAL) I am trying to learn more about triggers in mysql, so bear with me.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |