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?
Best Answer
Updating all rows in a table is seriously non-scalable.
Consider tossing
active
andupdated_at
. Changecreated_at
to beDEFAULT CURRENT_TIMESTAMP
(or something like that). And haveINDEX(created_at)
. Then,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.