0

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?

1
  • Your "active" field looks like excess one - simply make created_at field (and maybe updated_at too) auto-utilized. – Akina May 1 '20 at 10:10
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.