Mysql trigger to do an INSERT instead of an UPDATE

Question

Hello,

I would like to implement a basic versioning system in a MySQL table. Let's imagine a simple table with 2 columns: name (pk) and price. I thought I would simply add a column 'version' and add it to the primary key. Then I would catch all the UPDATE's and do an insert instead, incrementing the version number.

First, is this possible ? Can I make a trigger BEFORE UPDATE and do an insert and cancel the UPDATE ? What would be the syntax ? Second, is this idea ok ? how would you achieve this ?

Thank you for your help, Barth

Answer 1

You cannot cancel the update. I would keep table with versions separately from the "main" table, and would insert into this table new record when main table gets updated. Or even easier - use insert with new version number instead of update without any triggers.