MySQL Trigger: Can you turn a query updating multiple columns to multiple queries where each update one column?

Question

Here's what I'm trying to achieve: use a trigger to divide an update query which updates more than 1 columns into several update query and each of the query updates just 1 columns.

For example, the incoming query is:

update table set column_1 = 'a', column_2 = 'b', column_int = 100 where id = 1

I want to use trigger (or is there any other way?) to execute this query in the way:

update table set column_1 = 'a' where id = 1;
update table set column_2 = 'b' where id = 1;
update table set column_int = 100 where id = 1;

And of course after the three query we don't want to run the original update query (skip it to avoid run the query twice on the table).

Is this possible? The use case is that in MySQL you can update columns in a view, as long as the columns are from just 1 base table. So if I update two columns from different underlying base table MySQL will throw error, hence I want to use trigger to divide them.

Answer 1

I suggest that using a VIEW is the wrong starting point, and that TRIGGERs are just adding complication.

Instead of using Views and Triggers, have a "database layer" that segregates the Application code from the Database.

The layer could be written as subroutines in your programming language or as Stored Routines in MySQL.

The application would ask the layer to perform, say, an API like Set_Foobar('a', 'b', '100') and the function would do whatever it takes. Under the hood might be one UPDATE today, maybe 2 or 3 tomorrow.

With this technique, you have the freedom to rearrange the schema without touching the application code. But you will have to change the layer. And that will be easier than trying out how to implement the Trigger you are asking about.

Answer 2

Unfortunately IIRC mySQL does not support INSTEAD OF triggers which is how this could be done in other databases, and you can't cancel the action in a BEFORE UPDATE trigger without cancelling out the whole operation (by raising an error).

Even where INSTEAD OF triggers are supported I would generally suggest erring in the direction of Rick's suggestion and implementing this sort of multi stage process as an explicit method in your API rather than as magic background behaviour, though obviously you might have other considerations that make that impractical or otherwise less desirable.

The only other way that springs to mind would be to have a shadow table that mimics the view and have triggers on that which update the other tables - but that would be massively hacky. It would waste considerable space and opens up the possibility of bugs wrecking the integrity of your data in new and interesting ways.