0

I am getting MySQL error 1235, and have already tried all the solutions I could find on SO.

 --
 -- Trigger `buying_tasks`
 -- DELIMITER $$ CREATE TRIGGER `lock_user_of_task_buying_task` BEFORE INSERT ON  `buying_tasks`  FOR EACH ROW UPDATE  `users` SET locked =1
 WHERE id = NEW.user_id$$

With Error

   #1235 - This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

Here is the full action:

DELIMITER $$
CREATE TRIGGER `lock_user_of_task_buying_task` BEFORE INSERT ON `buying_tasks`
 FOR EACH ROW UPDATE `users` SET locked = 1 WHERE id = NEW.user_id
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `unlock_user_of_task_buying_task` AFTER DELETE ON `buying_tasks`
 FOR EACH ROW UPDATE `users` SET locked = 0 WHERE id = OLD.user_id
$$
DELIMITER ;

This is a dump of my old database file I am trying to import.

Thanks!

William Perron
  • 485
  • 7
  • 16
Jame
  • 1
  • 1
  • 1
    Possible duplicate of http://stackoverflow.com/questions/19016297/mysql-error-code-1235 – Louys Patrice Bessette May 24 '16 at 21:14
  • Already tried the solutions there. – Jame May 24 '16 at 21:20
  • Did you try END$$ instead of only $$ before DELIMITER ; ? see : http://www.mysqltutorial.org/getting-started-with-mysql-stored-procedures.aspx – Louys Patrice Bessette May 24 '16 at 21:36
  • Read this too : http://www.lemiffe.com/5-solutions-for-mysql-error-1235/ – Louys Patrice Bessette May 24 '16 at 21:38
  • @LouysPatriceBessette Gives me a syntax error. – Jame May 24 '16 at 21:43
  • Hew... And what about defining the delimiter only once and use it between you queries... Like here : http://blog.sqlauthority.com/2013/12/27/mysql-how-to-change-delimiter-in-mysql-supporting-go-keyword-of-sql-server-in-mysql/ To be honest, I'm searching the web with you on this. – Louys Patrice Bessette May 24 '16 at 21:59
  • Nope, here is what I am trying now: http://pastebin.com/RzHrNWX4 – Jame May 24 '16 at 22:07
  • @LouysPatriceBessette I also see no other BEFORE INSERT anywhere in the file. – Jame May 24 '16 at 22:24
  • Have you Tryed «BEFORE UPDATE» and «AFTER UPDATE» instead of the insert and delete event that do not occur anyway ? – Louys Patrice Bessette May 24 '16 at 22:27
  • @LouysPatriceBessette The only BEFORE/AFTER UPDATE http://pastebin.com/g9eEaDpS – Jame May 24 '16 at 22:29
  • I'm getting lost. Your Pastebin changed a lot. I just found a mySQL debugger that may be usefull to you : https://www.devart.com/dbforge/mysql/studio/code-debugger.html Good luck! – Louys Patrice Bessette May 24 '16 at 22:38
  • Your code is correct. Please check if your triggers exists already: `show triggers where ``table`` like 'buying_tasks';` If they don't exist, please check again. – Solarflare May 24 '16 at 22:41
  • @Solarflare http://i.imgur.com/guyJHu5.png – Jame May 24 '16 at 23:10
  • @Jame Your screenshot shows your triggers have been created. I'm a little confused as to what is your problem. – Solarflare May 24 '16 at 23:51
  • Me too, if it is creating them why would it error? – Jame May 24 '16 at 23:53
  • When will it give the error exactly? When you run your code/import again after they are already there? That can't be, since you "have already tried all the solutions I could find on SO.", and dropping the trigger before creating them again should be the first solution you should find for this problem on SO. But just in case you didn't find that by yourself and didn't bother to read the link in the first comment with the same obvious solution: DROP your already existing trigger before you create them again! If that doesn't solve it: Please describe in detail what you are doing. – Solarflare May 25 '16 at 00:03

0 Answers0