update all rows after insertion in mysql

Question

I am working with mysql and I have a table with the following structure (a summary):

CREATE TABLE `costs` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`utility` DECIMAL(9,2) UNSIGNED NOT NULL,
`tax` DECIMAL(9,2) UNSIGNED NOT NULL,
`active` TINYINT(1) UNSIGNED NOT NULL DEFAULT '1',
`created_at` TIMESTAMP NULL DEFAULT NULL,
`updated_at` TIMESTAMP NULL DEFAULT NULL,
PRIMARY KEY (`id`),
)

where the active field defaults to 1 when inserting, then i would like when saving a new record all other rows update the active field as 0, so i try to create a trigger for this but i am getting a mysql error.

DELIMITER //
CREATE TRIGGER after_costs_insert AFTER INSERT ON costs FOR EACH ROW 
BEGIN
  UPDATE costs SET active = 0 WHERE id <> NEW.id;
END;
//
DELIMITER ;

I think it is not possible to do this, so how can I update these rows?

Answer 1

Updating all rows in a table is seriously non-scalable.

Consider tossing active and updated_at. Change created_at to be DEFAULT CURRENT_TIMESTAMP (or something like that). And have INDEX(created_at). Then,

SELECT ...
    ORDER BY created_at DESC  LIMIT 1

Will get the "latest" row (which seems to be your "active").

If there is a risk of inserting two rows in the same second, there are workarounds.