Let's say that I want to have a table that logs the date and the number of columns in some other table (or really any sort of math / string concat etc).

CREATE TABLE `log` (

`id` INTEGER NOT NULL AUTO_INCREMENT ,

`date` DATETIME NOT NULL ,

`count` INTEGER NOT NULL ,

PRIMARY KEY (`id`)

);

Is it possible to have the count column calculated for me whenever I do an insert?

e.g. do something like:

INSERT INTO log (date='foo');

and have count calculated by mysql.

Obviously I could do it myself by doing a query to get the count and inserting it, but this would be better.

解决方案

Triggers are the best tool for annotating data when a table is changed by insert, update or delete.

To automatically set the date column of a new row in the log with the current date, you'd create a trigger that looked something like this:

create trigger log_date before insert on log

for each row begin

set new.date = current_date()

end;