1

Thanks in advance for the help. I looked all over and couldn't find an example quite like what I'm needing help with. I'm creating a trigger to update a table after insert but I don't know the table name to update until after the insert happens. This is the code I'm trying to use, but I get an error.

BEGIN
  SET @ven = NEW.`ven_code`;
  SET @ventable = concat('pp_ven_',@ven);
  UPDATE @ventable SET `stock_qty`=NEW.`endingStock` WHERE `iin`=NEW.`iin`;
END
Mike Thiel
  • 57
  • 7

2 Answers2

2

This is not possible as described with dynamical sql / Prepared Statement. It would generate Error Code: 1336. Dynamic SQL is not allowed in stored function or trigger upon the attempt to even CREATE TRIGGER.

About the closest you could get to automation is to use CREATE EVENT. Events are scheduled stored programs that run on the schedule / Interval of your choosing. The intervals are:

interval:

quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
          WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
          DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

Your could set a "flag" so to speak on a row, such as the table your are depicting above that has the After Insert trigger. The event could then perform the Prepared Statement dynamically and execute it.

See my answer here on Event Management.

I have to say that even if run in an event, what you are proposing is almost Always the sign of a poor schema design that wouldn't hold up well to peer review.

A reason why the dynamic sql and a Prepared Stmt is disallowed is because the trigger needs to be fast, and even DDL could be snuck into the string and executed. And DDL stmts like ALTER TABLE are disallowed in triggers(they could take hours to run literally).

Your schema could just as well have one table shared with a column ven_code being the differentiator column. Instead you chose to create new tables for each ven_code. That typically is a poor design and performance choice.

If you need help with schema design, I am happy to chat about it with you in a chat room.

Community
  • 1
  • 1
Drew
  • 24,851
  • 10
  • 43
  • 78
  • Great answer! Thank you very much. I could just as well update the table in question in my PHP but I'm just getting into working with triggers and thought it would be a good implementation. I was wrong.. haha.. basically I have a vendor specific product tables so that I can have smaller tables and only query the specific one when needed.. maybe that is a poor way to do it, I'm not sure. But then I have a stock tracking table which is just history of the stock events. I was trying to update the vendor product table's stock column when the history record was inserted. Chatting would be great! – Mike Thiel Sep 13 '16 at 15:42
  • Do as I say not as I do (as they say). I do the same myself with small tables. But were I to work with a lot of peers I would change my ways. I like tables with 10 to 20 K rows for speed and cache and lack of indexes at times versus much larger table. Mainly cuz there is so much deadwood in them. – Drew Sep 13 '16 at 15:45
  • We have a mysql-ish room here [Campaigns](http://chat.stackoverflow.com/rooms/95290) so stop on by and ping Rick or myself – Drew Sep 13 '16 at 15:46
1

You should look for "prepared statements"

This answer might be helpful:

How to select from MySQL where Table name is Variable

Community
  • 1
  • 1
DarioDF
  • 301
  • 1
  • 10
  • This is disallowed in a Trigger. It would generate `Error Code: 1336. Dynamic SQL is not allowed in stored function or trigger` upon the attempt to even `CREATE TRIGGER` – Drew Sep 13 '16 at 05:11