Mike Thiel Mike Thiel - 2 months ago 10
MySQL Question

In MySQL trigger, is it possible to set a user variable with NEW.col and use that in update query?

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

Answer

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.