MySQL has a nice feature that you may well not have used called Triggers.
These are little SQL statements that are tied to events on table and then perform some extra work.
The idea being that you will check the values for a row and if they meet some conditions you will take some further action. You might usually do this kind of thing in code but if you really must be certain that in a certain circumstance a certain action will be taken, then triggers are a good option because regardless which bit of code makes the change you can be sure your rules will be enforced.
Of course this ties you to a specific DB platform so its not something you should over use. There can also be performance issues so you need to bear that in mind.
Anyway, assuming you know what you are letting yourself in for and have read the documentation here is my little summary.
First you need to give your trigger a name. Choose something descriptive.
Then you need to associate it to an even (UPDATE, INSERT etc) and also decide whether to do it BEFORE or AFTER. If you are not sure, use BEFORE – it’s generally the one you want to use.
CREATE TRIGGER do_something BEFORE UPDATE ON table ...
The next bit is boiler plate as far as I can gather, just whack it in. It delimits your actual statement and tells it to be run for each updated row.
FOR EACH ROW BEGIN
Then do your test
IF ((NEW.column < 1) AND (OLD.column >=1)) THEN
Note the NEW and OLD keywords there.
You have a choice of using the NEW and OLD keyword to access existing (OLD) and about to be set (NEW) data for each column.
Note also, this is only possible if you use the BEFORE event as opposed to AFTER, hence generally just sticking to using BEFORE
Now, enforce your requirements
SET NEW.another_column = 0; SET NEW.and_another_column = 0; SET NEW.and_another = 'cheese';
Then wrap it up
END IF; END;
There’s another bit that I totally missed which caused me issues – you need to wrap the whole thing in a delimiter statement:
delimiter // ... the statement END;// delimiter;
So the full final thing should look like this:
delimiter // CREATE TRIGGER do_something BEFORE UPDATE ON table FOR EACH ROW BEGIN IF ((NEW.column < 1) AND (OLD.column >=1)) THEN SET NEW.another_column = 0; SET NEW.and_another_column = 0; SET NEW.and_another = 'cheese'; END IF; END; // delimiter;
Another point to mention is – just use the CLI for handling these, don’t try to use phpMyAdmin etc – it doesn’t really work.