This is a discussion on Help about Trigger, user defined variables, Error Code : 1064. within the MySQL Database forums, part of the Database Forums category; Hello everybody, I am newbe about databases and I am starting with mysql. I have a trouble with trigger: I ...
|
|||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
|
|||
|
Hello everybody,
I am newbe about databases and I am starting with mysql. I have a trouble with trigger: I wrote a working trigger which II would like to make more general using user defined variables in the trigger. The working trigger is: DELIMITER $$; CREATE TRIGGER `db_LabELT`.`ins_synch_status_prefix ` AFTER INSERT on `db_LabELT`.`anagrafica` FOR EACH ROW BEGIN Insert INTO componentsliststatus SET IdComponente = NEW.IdComponente; Update componentsliststatus SET Anagrafica='1' where IdComponente = NEW.IdComponente; Update componentsliststatus SET AnalisiVisiva='0' where IdComponente = NEW.IdComponente; Update componentsliststatus SET AnalisiStrumentale='0' where IdComponente = NEW.IdComponente; Insert INTO instrumentalanalysis_EGR SET IdComponente = NEW.IdComponente; Insert INTO visualanalysis_EGR SET IdComponente = NEW.IdComponente; END$$ DELIMITER ;$$ I would like to stress than for an unknow reason if I set several field in the same line, I get error. So I had to workaround using several update statement (sigh ... !!!!) I need to make more have the tables name in the latest two insert more general, e.g. visualanalysis_EGR, visualanalysis_GCU, visualanalysis_GLO, etc so I added the @va user variable to describe the table's name: DELIMITER $$; CREATE TRIGGER `db_LabELT`.`ins_synch_status_prefix ` AFTER INSERT on `db_LabELT`.`anagrafica` FOR EACH ROW BEGIN SET @VA := CONCAT('visualanalysis_',substr(NEW.IdComponente,1 ,3)); Insert INTO componentsliststatus SET IdComponente = NEW.IdComponente; Update componentsliststatus SET Anagrafica='1' where IdComponente = NEW.IdComponente; Update componentsliststatus SET AnalisiVisiva='0' where IdComponente = NEW.IdComponente; Update componentsliststatus SET AnalisiStrumentale='0' where IdComponente = NEW.IdComponente; Insert INTO instrumentalanalysis_EGR SET IdComponente = NEW.IdComponente; Insert INTO @VA SET IdComponente = NEW.IdComponente; END$$ DELIMITER ;$$ I get the following error message: (0 row(s) affected) (0 ms taken) Error Code : 1064 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 '@VA SET IdComponente = NEW.IdComponente; END' at line 9 (0 ms taken) Could pls someone help me, at least the right direction to solve this issue. Many thanks, MassimoM |