CODEWITHSUNDEEP
mysqlversioninginnodb
donk
donk

Reputation: 1570

InnoDB automatic actions

I'm using MySQL and InnoDB. I'm versioning all the table's records - the latest version of the record has a column called 'lv' set to 1 and the previous record for that ID that has an 'lv' column set to 1 has to be set to 0 now that there's a new version. I'm now doing two queries every time I insert a new record (version). Is there a way to make InnoDB do that automatically? Thank you for your input.

Upvotes: 1

Views: 72

Answers (1)

Nanne
Nanne

Reputation: 64409

You could use a trigger?

This is untested, but I think something like this would be possible:

delimiter |

CREATE TRIGGER newversion BEFORE INSERT ON yourTable
  FOR EACH ROW BEGIN
    UPDATE yourTable SET lv = 0 WHERE lv = 1;
  END;
|

delimiter ;

There could be some mistake in there, as it is a quick type, based on the manual ofcourse :)

Upvotes: 1

Related Questions